I'm producing two pivoted data sets:
Data set 1:
let T1 =
data
| where col1 == "blah"
| evaluate pivot(col2, count(col2), col3, col4);
Data set 2:
let T2 =
data
| where col1 == "blahblah"
| evaluate pivot(col2, count(col2), col3, col4);
Both of these data sets produce the information that I want. But I'd like to see them all in 1 table so I join them..
T1
| join kind=leftouter T2 on col3,col4
When I join these two data sets the record sets join, but the pivoted counted columns become a multiplied by 4. I've tried explicitly defining left and right in several ways.
Conbinations of:
| join kind=leftouter T2 on $left.col3 == $right.col3, $left.col4 == $right.col4
| join kind=leftouter T2 on ($left.col3 == $right.col3, $left.col4 == $right.col4)
| join kind=leftouter T2 on ($left.col3 == $right.col3 and $left.col4 == $right.col4)
Is there a syntax for multiple column joins? I can't find any reference to multiple columns in the documentation.
I've also tried creating one column to join on and still get the 4 time multiplier -Wondering if this is a bug in Kusto?
e.g. Data set 1:
let T1 =
data
| where col1 == "blah"
| evaluate pivot(col2, count(col2), col3, col4)
| extend joiny=strcat(col3,col4);
Data set 2:
let T2 =
data
| where col1 == "blahblah"
| evaluate pivot(col2, count(col2), col3, col4)
| extend joiny=strcat(col3,col4);
and then join:
T1
| join kind=leftouter T2 on joiny
This still results in a dataset where all the pivoted columns are multiplied by 4.
Any help / syntax / advise would be helpful! Thanks.
EDIT
I've since removed the use of materialize() around these two data sets and the behavior is as expected. It's now working, but I'm still at a loss as to why.
