CODEWITHSUNDEEP
azure-data-explorerkql
Dalibor Trišić
Dalibor Trišić

Reputation: 93

Kusto query to get the latest column value which is not empty (for each column)

The easiest way to explain what I want to accomplish is with this example:

let data = datatable(id:int, b:string, c:string, dateTime:datetime)
[
    1,"","c one","2021-03-03",
    2,"b one","","2021-03-04",
    3,"","c two","2021-03-05"
];

As you can see, id and date values are present for every row. Values for b and c columns are optional. The goal would be to get, in one row, the latest value for each column, when that value is not empty. Result should look like:

3, "b one", "c two", "2021-03-05"

In real scenario there are much more columns and I am wondering is it possible to do it on an easy way, without writing too much queries.

Upvotes: 2

Views: 3218

Answers (1)

Alexander Sloutsky
Alexander Sloutsky

Reputation: 3017

Assuming there is an order of the data as you defined - you can use arg_max() aggregation: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/arg-max-aggfunction

let data = datatable(id:int, b:string, c:string, dateTime:datetime)
[
    1,"","c one","2021-03-03",
    2,"b one","","2021-03-04",
    3,"","c two","2021-03-05"
];
['data']
| serialize n = row_number()
| summarize arg_max(iif(isnotnull(id), n, -1), id),
            arg_max(iif(isnotempty(b), n, -1), b),
            arg_max(iif(isnotempty(c), n, -1), c),
            arg_max(iif(isnotnull(dateTime), n, -1), dateTime)
| project-away max_*

Upvotes: 6

Related Questions