Forum Discussion
Summarize dynamic array?
Yoni Sorry, I misunderstood. Here's my exact example.
I have a string array called 'Tiles' which looks like ["1", "2", "3", ""]
My ultimate goal is to summarize 'Tiles' (ignoring the last element in the array since it is always empty), to show the most common number in each element of the array for around 100 different values for 'Tiles'.
For example, let's say 5 values of 'Tiles' are:
["1", "2", "3", ""]
["1", "12", "9", ""]
["12", "12", "7", ""]
["4", "6", "9", ""]
["9", "5", "5", ""]
The goal would be to produce an output that shows:
| Tile | Most Common Number |
| 1 | 1 |
| 2 | 12 |
| 3 | 9 |
The furthest I've gotten is splitting the array into integer variables and seeing how many times a given value appears for the first element in the array (sorry for bad code example):
let winner_chose_tiles = ['events.all'] | where FullName_Name == "winner_chose_tiles";
winner_chose_tiles
| project Entity_Id, Tiles = split(EventData["Tiles"], ",")
| project Tile1 = toint(Tiles[0]), Tile2 = toint(Tiles[1]), Tile3 = toint(Tiles[2]), Entity_Id
| summarize FirstTile = count() by Tile1
Microsoftbased on my understanding of your description, this should work (like in the original example). if my understanding isn't correct, please provide the input data set using the `datatable` operator, so that it's clearer how your input data set looks like
datatable(EventData:dynamic) [ dynamic({"Tiles":["1", "2", "3", ""]}), dynamic({"Tiles":["1", "12", "9", ""]}), dynamic({"Tiles":["12", "12", "7", ""]}), dynamic({"Tiles":["4", "6", "9", ""]}), dynamic({"Tiles":["9", "5", "5", ""]}), ] | mv-expand with_itemindex = Tile Value = EventData.Tiles | where isnotempty(Value) | summarize c = count() by Value = toint(Value), Tile = Tile + 1 | summarize arg_max(c, Value) by Tile | project-away c
this returns:
| Tile | Value |
| 1 | 1 |
| 2 | 12 |
| 3 | 9 |
Yoni That works great, thank you!!
Lastly, how can I summarize based on the entirety of the datatable?
For example, treat each element in each array as its own value, and then list the top three most common values?
So if the data was:
["1", "2", "3", ""]
["4", "1", "1", ""]
["5", "4", "1", ""]
["6", "4", "2", ""]
Then the output would be (doesn't really matter what order):
Value 1 4 2 - Yoni
datatable(EventData:dynamic) [ dynamic({"Tiles":["1", "2", "3", ""]}), dynamic({"Tiles":["4", "1", "1", ""]}), dynamic({"Tiles":["5", "4", "1", ""]}), dynamic({"Tiles":["6", "4", "2", ""]}), ] | mv-expand Value = EventData.Tiles | where isnotempty(Value) | summarize count() by toint(Value) | top 3 by count_ | project Value
Yoni Awesome, works great, thank you for all your help! I have a lot to learn but you've helped a huge amount.