CODEWITHSUNDEEP
joinazure-data-explorerkql
Kosh
Kosh

Reputation: 1246

KQL: merging 2 columns after joining tables

I want to merge 2 columns on which I joined 2 tables. Here is my code:

let Table1 = datatable(ver: string, number:int) 
[
1.0, 5, 
2.0, 5,
2.1, 3 
];
//
let Table2 = datatable(ver: string, number:int) 
[ 
2.0, 3,
2.1, 2,
3.0, 1 
];
//
Table2
| join kind = fullouter Table1 on ver
| extend Delta = number1 - number

This is what I get:

enter image description here

And this is what I need:

enter image description here

Upvotes: 4

Views: 5996

Answers (1)

Yoni L.
Yoni L.

Reputation: 25955

you could use the coalesce() function: https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/coalescefunction

for example:

let Table1 = datatable(ver:string, number:int)
[
    '1.0', 5,
    '2.0', 5,
    '2.1', 3
];
let Table2 = datatable(ver:string, number:int)
[
    '2.0', 3,
    '2.1', 2,
    '3.0', 1
];
Table2
| join kind = fullouter Table1 on ver
| project ver = coalesce(ver, ver1),
          Delta = coalesce(number1, 0) - coalesce(number, 0)
ver Delta
1.0 5
2.0 2
2.1 1
3.0 -1

Upvotes: 7

Related Questions