johnwrite - 1 year ago 120

MySQL Question

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

`customEvents`

| 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

By this I am able to get the common rows on unique value Z in column2 and X in column1 .

Now how to show the remaining rows of column1 having Y value alone in event A and add them to the result set that I got from the above inner join query ? I tried using a full outer with event A having Y value alone with the above result i got . But it doesn't gave me desired results. Need some help on this .

Answer Source

I think there are several ways to achieve the desired outcome:

Use

`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.Use

`(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.