Need to Join two tables and count key from first table and second table on every record from first table
let T = datatable(TId:int, TName:string, Tkey:string)
[
1, "A", "xyz",
2, "B", "xyz",
3, "C", "yza",
];
let u = datatable(UId:int, UName:string, Ukey:string)
[
1, "bla1", "xyz",
2, "bla2", "xyz",
30, "bla3", "xyz",
12, "bla5", "xyz",
80, "bla9", "xyz",
4, "bla11", "cde",
55, "bla12", "yza",
96, "bla21", "yza",
];
Expected result
let T = datatable(TId:int, TName:string, Tkey:string)
[
1, "A", "xyz",
2, "B", "xyz",
3, "C", "yza",
];
let u = datatable(UId:int, UName:string, Ukey:string)
[
1, "bla1", "xyz",
2, "bla2", "xyz",
30, "bla3", "xyz",
12, "bla5", "xyz",
80, "bla9", "xyz",
4, "bla11", "cde",
55, "bla12", "yza",
96, "bla21", "yza",
];
let T_sum = T | summarize TkeyCount = count() by Tkey;
let u_sum = u | summarize UkeyCount = count() by Ukey;
T
| extend Ukey = Tkey
| lookup kind=leftouter T_sum on Tkey
| lookup kind=leftouter u_sum on Ukey
TId | TName | Tkey | Ukey | TkeyCount | UkeyCount |
---|---|---|---|---|---|
1 | A | xyz | xyz | 2 | 5 |
2 | B | xyz | xyz | 2 | 5 |
3 | C | yza | yza | 1 | 2 |