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
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:
