1 Reply Latest reply: Nov 16, 2017 11:20 AM by balkumar chandel RSS

    Left join with different granularity of data

    balkumar chandel

      I have a requirement to populate a new column in Alert table as ‘Alert Status ‘ by checking into Ticket tables which I have TicketwithL3Node and TicketwithL4Node both the tables have the same number (Name) of columns just don’t have value for ‘L4Node’ in TicketwithL3Node.

       

      So if I do left join with Alert table and TicketwithL3Node it should be with Key L3Node

      And if I do left join with Alert table and TicketwithL4Nodeit should be with Key L3Node&’|’& L4Node as Key

       

      And finally

      if TicketType =’Performace’ then Alert Status should populate as ‘Ticket raised’

      if TicketType =Comment then Alert Status should populate as ‘No Action Required’

      if TicketType =Null() then Alert Status should populate as ‘No Action taken’

       

      Tried- after doing left join separately for both the tables with Alert , concatenated the tables but it is not giving correct count of alert because of concatenation. ( Can’t Do distinct count as Alert number can be multiple)

       

      Please advise if somebody worked with same type of problem.

       

      Please find sample data.

       

      Let me know if you need any more information.

      Any help will be appreciated.

       

      Thanks,

      BKC

        • Re: Left join with different granularity of data
          balkumar chandel

          Resolved by using the combination of inner join , concatenation and where exists.

          Below is the sample code.

           

          Alert:

           

          LOAD Alert,

              L3,

          //L3&'|'& L4 as Key,

               L3 as AlertL3,

               L4 as AlertL4

          FROM

          [L3andL4TestData.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          inner Join(Alert)

           

          Ticket:

          LOAD Ticket,

              

               L3,

               L4,

               'L3' as Flag

          FROM

          [L3andL4TestData.xlsx]

          (ooxml, embedded labels, table is Sheet2);

           

           

           

          store Alert into $(vqvdpath)\1.Extract\Alert.qvd (qvd);

           

          drop Table Alert;

           

           

          Alert1:

           

          LOAD Alert,

          L3&'|'& L4 as Key,

               L3 as AlertL3,

               L4 as AlertL4

          FROM

          [\L3andL4TestData.xlsx]

          (ooxml, embedded labels, table is Sheet1);

           

          inner Join(Alert1)

           

          LOAD Ticket,

          L3&'|'& L4 as Key,

               L3 ,

               L4,

               'L4' as Flag

          FROM

          [\L3andL4TestData.xlsx]

          (ooxml, embedded labels, table is Sheet3);

           

          store Alert1 into $(vqvdpath)\Alert1.qvd (qvd);

           

          drop table Alert1;

           

          AlerFinal:

          LOAD L3,

               Alert,

               AlertL3,

               AlertL4,

               Ticket,

               L4,

               Flag

          FROM

          [$(vqvdpath)\Alert.qvd]

          (qvd);

           

          Concatenate

           

          LOAD Key,

               Alert,

               AlertL3,

               AlertL4,

               Ticket,

               L3,

               L4,

               Flag

          FROM

          [$(vqvdpath)\Alert1.qvd]

          (qvd);

           

          Concatenate (AlerFinal)

           

          Alert2:

           

          LOAD Alert,

              L3,

               L3 as AlertL3,

               L4 as AlertL4

          FROM

          [\L3andL4TestData.xlsx]

          (ooxml, embedded labels, table is Sheet1) where not Exists (L3);

          // Composite key can be used in where exists

           

           

          Thanks,

          BKC