Forum Discussion

bphillips09's avatar
bphillips09
Copper Contributor
Aug 16, 2019

Summarize dynamic array?

Hi, I apologize for my lack of experience, however this is literally my first time using / learning about Azure Data Explorer.    I have this data:    | project Data1 = Data[0], Data2 = Data[1],...
Azure Data Explorer (Kusto)
Kusto language
Yoni's avatar
Yoni
Icon for Microsoft rankMicrosoft
Aug 16, 2019

Here's one option:

 

datatable(Data:dynamic)
[
    dynamic([9,  12, 24]),
    dynamic([17, 12, 37]),
    dynamic([9,  17, 37]),
]
| mv-expand with_itemindex = ArrayIndex Data to typeof(int)
| summarize c = count() by Data, ArrayIndex
| summarize arg_max(c, Data) by ArrayIndex
| project-away c

 

 

 

  • bphillips09's avatar
    bphillips09
    Copper Contributor
    Aug 16, 2019

    Yoni Thanks for the reply! However, these sets aren't pre-programmed sets, i.e. would it be possible to create a datatable like this?:

    datatable(Data:dynamic)
[
    dynamic([Data1, Data2, Data3])
]

    Where Data1, Data2, and Data3 are three distinct variables that change? 

    • Yoni's avatar
      Yoni
      Icon for Microsoft rankMicrosoft
      Aug 16, 2019

      using the `datatable` operator in that sample was just in order to have some input data set. the "point" (answer to your original question) was in the lines starting from `| mv-expand ...`

       

      if that doesn't solve your issue -perhaps, you can show a sample of how your _real_ data looks like, so that we can work with that?

      • bphillips09's avatar
        bphillips09
        Copper Contributor
        Aug 17, 2019

        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:

        TileMost Common Number
        11
        212
        39

         

        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

         

Resources