I have 2 custom events A and B . A can have values either "X" or "Y" . B will have a unique value Z . Some rows of event A has both values "X" and "Y" but has a unique event B value "Z" and I want to filter this event A having values "X" and "Y" .
I want the result set to be having event A with only value X and event B with value Z . Basically I doesn't want to show the entry of event A having Y value when it already has an entry having "X" with the same unique value Z that it has with Y.
I basically did an inner join between values of X and Y based on unique value Z
| extend Column1 = tostring(customDimensions.['A']) , column2 = tostring(customDimensions.['B'])
| where (Column1 == "X" )
| project Column1 ,column2
| join kind= inner (
customEvents | extend Column1 = tostring(customDimensions.['A']) , column2 = tostring(customDimensions.['B']) | where (Column1 == "Y"
)| project Column1 , column2
) on column2 | project Column1 , column2
I think there are several ways to achieve the desired outcome:
Union operator that just "glues" two data sets together without joining on any field -
(Query1) | union (Query2). This is suitable if you can have two data sets in two queries as an input to produce the union.
(Query1) | join kind=anti (Query2) on Field to get everything from the left side that is not represented in the right side in
Field. This is suitable if you have a data set you want to "subtract" from the other richer data set to get the required result.