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:
And this is what I need:
CodePudding user response:
you could use the coalesce()
function: https://docs.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 |