I need to compare value in multiple column (up to 10) and to return the column name where there is the max value (for each row).

I did some try with array_sort_asc() but I'm not able to create a proper array with column "value".

Example :

Application 1	Application 2	Application 3
3	20	1

I need to compare value in multiple column (up to 10) and to return the column name where there is the max value (for each row).

I did some try with array_sort_asc() but I'm not able to create a proper array with column "value".

Example :

Application 1	Application 2	Application 3
3	20	1

I need to get "Application 2" as output

Any idea ? :) thanks

• kql
• azure-data-explorer

Share Improve this question Follow asked Jan 31 at 9:49 PierrotPierrot 1911 silver badge44 bronze badges 2

• Could you please share what you tried? – Balaji Commented Jan 31 at 10:10
• @Balaji I stuck to create a dynamic with column value. | extend array = dynamic (Application1, Application2, Application3) it's not accepted – Pierrot Commented Jan 31 at 10:15

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Try with the below code, it uses pack() function to create dynamic JSON object. It uses bag_keys(Apps) to extracts the keys (Application 1, Application 2, Application 3) as an array and mv-expand splits the keys into separate rows. Now convert AppKey to a string and extract the corresponding value from the Apps. Use arg_max(AppValue, AppKey) to find the application with the highest value in each row.

let data = datatable(Application_1:int, Application_2:int, Application_3:int)
[
    3, 20, 1,
    15, 8, 22,
    5, 25, 10
];

data
| extend Apps = pack('Application 1', Application_1, 'Application 2', Application_2, 'Application 3', Application_3) 
| mv-expand AppKey = bag_keys(Apps)
| extend AppKey = tostring(AppKey)  
| extend AppValue = toint(Apps[AppKey])  
| summarize MaxApplication = arg_max(AppValue, AppKey) by Application_1, Application_2, Application_3
| project Application_1, Application_2, Application_3, MaxApplication=AppKey

Output: