Aggregating Column Values In Kusto

0
votes

How do I transform kusto data that looks like this:

let fauxData = datatable (OrgName:string, Status:string, EastUS:long, SouthCentralUS:long, WestUS2:long)
['Apple', 'Red',    50, 10, 90,
'Apple', 'Orange', 30, 30, 10,
'Apple', 'Yellow', 10, 0, 0,
'Apple', 'Green', 10, 60, 0,
'Ball', 'Red', 20, 20, 20,
'Ball', 'Orange', 30, 30, 30,
'Ball', 'Yellow', 0, 0, 0,
'Ball', 'Green', 50, 50, 50,
];

To look like this:

['Apple', 'ComboOfRedandOrange', 80, 40, 100,
'Apple', 'ComboOfGreenandYellow', 20, 60, 0,
'Ball', 'ComboOfRedandOrange', 50, 50, 50,
'Ball', 'ComboOfGreenandYellow', 50, 50, 50,
]
2
azure-data-explorerkql

2 Answers

2
votes

You can use next query to achieve your goal:

let fauxData = datatable (OrgName:string, Status:string, EastUS:long, SouthCentralUS:long, WestUS2:long)
['Apple', 'Red',    50, 10, 90,
'Apple', 'Orange', 30, 30, 10,
'Apple', 'Yellow', 10, 0, 0,
'Apple', 'Green', 10, 60, 0,
'Ball', 'Red', 20, 20, 20,
'Ball', 'Orange', 30, 30, 30,
'Ball', 'Yellow', 0, 0, 0,
'Ball', 'Green', 50, 50, 50,
];
fauxData
| extend combo = case(Status in ('Red', 'Orange'), 'ComboOfRedandOrange',
                      Status in ('Green', 'Yellow'), 'ComboOfGreenandYellow',
                     'Unknown')
| summarize sum(EastUS), sum(SouthCentralUS), sum(SouthCentralUS) by OrgName, combo
1
votes

you could try something like this:

let T = 
    datatable(OrgName: string, Status: string, EastUS: long, SouthCentralUS: long, WestUS2: long)
    [
        'Apple', 'Red', 50, 10, 90,
        'Apple', 'Orange', 30, 30, 10,
        'Apple', 'Yellow', 10, 0, 0,
        'Apple', 'Green', 10, 60, 0,
        'Ball', 'Red', 20, 20, 20,
        'Ball', 'Orange', 30, 30, 30,
        'Ball', 'Yellow', 0, 0, 0,
        'Ball', 'Green', 50, 50, 50,
    ]
;
let F = (statuses:dynamic)
{
    T
    | where Status in(statuses)
    | summarize sum(EastUS), sum(SouthCentralUS), sum(WestUS2) by OrgName, Status = strcat("ComboOf", strcat_array(statuses, "And"))
}
;
union 
 F(dynamic(['Red', 'Orange'])),
 F(dynamic(['Green', 'Yellow']))
| order by OrgName asc, Status asc