CODEWITHSUNDEEP
c#mysqlsqlazureazure-application-insights
johnwrite
johnwrite

Reputation: 85

Azure Application Insights Analytics Query on Joining

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 .

Upvotes: 3

Views: 7935

Answers (2)

Dmitry Matveev
Dmitry Matveev

Reputation: 2679

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.

Upvotes: 2

John Gardner
John Gardner

Reputation: 25116

can't you do this even simpler without any joins/unions at all, something like

customEvents | 
extend columnA = tostring(customDimensions.['A'])
extend columnB = tostring(customDimensions.['B'])
extend columnMerged = iff( columnA == 'X', ColumnA, ColumnB )
project columnMerged

Upvotes: 0

Related Questions