Home > Software engineering >  Save order when using top-nested Kusto operator
Save order when using top-nested Kusto operator

Time:06-02

I'm trying to produce a hierarchical aggregation by using top-nested Kusto operator for export to xlsx and works well but i'm loosing my default order by "Class" and "Date" fields by using query:

Test | top-nested 25 of Class by sum(Value),  top-nested 25 of Date by sum(Value)

my data in db my data in db looks like in this file

expected result on this image (same as above but Order by Class) query:

Test | where (tolower(Class) == tolower('Väganläggning'))| summarize sum(Value) by Date | take 25

enter image description here

but i'm getting data with strange order for "Date" and "Class" like on this exported xlsx file (first "Date" 2029 year instead 2009 and so on)

enter image description here

Seems i need query like this

Test | where Class == prev(Class) | top-nested 25 of Class by sum(Value), top-nested 25 of Date by sum(Value)

but i'm not experienced in Kusto, so need help guys)

CodePudding user response:

The last dimension column goes to the Values section in the Excel Pivot.
The rest of the columns goes to the Rows section.

// Generate data sample. Not part of the solution.
let Test = materialize
(
    range i from 1 to 1000000 step 1 
    | extend Class = strcat("Class-", tostring(toint(rand(10))))
            ,Date  = startofday(ago(1d*rand(30)))
            ,Value = rand()
            ,Dim1  = strcat("Dim1-", tostring(toint(rand(10))))
            ,Dim2  = strcat("Dim2-", tostring(toint(rand(10))))
            ,Dim3  = strcat("Dim3-", tostring(toint(rand(10))))
);
// Solution starts here.
Test
| top-nested 4 of Class by sum(Value)
 ,top-nested 3 of Date  by sum(Value)
 ,top-nested 2 of Dim1  by sum(Value)
 ,top-nested 2 of Dim2  by sum(Value)
 ,top-nested 2 of Dim3  by sum(Value) 
| order by aggregated_Class ,Class asc
          ,aggregated_Date  ,Date  asc
          ,aggregated_Dim1  ,Dim1 asc
          ,aggregated_Dim2  ,Dim2 asc
          ,aggregated_Dim3  ,Dim3 asc
| project-rename Values = aggregated_Dim3
| project-away aggregated_*
Class Date Dim1 Dim2 Dim3 Values
Class-1 2022-05-10T00:00:00Z Dim1-2 Dim2-8 Dim3-8 5.1418120760105817
Class-1 2022-05-10T00:00:00Z Dim1-2 Dim2-8 Dim3-4 4.8139672995169027
Class-1 2022-05-10T00:00:00Z Dim1-2 Dim2-5 Dim3-4 3.6333999871855704
Class-1 2022-05-10T00:00:00Z Dim1-2 Dim2-5 Dim3-0 3.5165481405530339
Class-1 2022-05-10T00:00:00Z Dim1-6 Dim2-9 Dim3-3 3.526092609498761

(Only a sample of the output)

Excel Pivot

  • Related