8 Replies Latest reply: Mar 21, 2015 8:02 AM by wrvs fsbv RSS

    Left join with distinct

    Anosh Nathaniel

      Hi,

      I would like to understand the different between Left Join and Left Join with distinct keyword with the below script.

       

      With distinct keyword:

      tab1:

      LOAD * INLINE [

          id, value

          1, a

          2, b

          3, c

          3, c

      ];


      left join(tab1)

      LOAD distinct * INLINE [

          id, field

          1, aa

          2, bb

          3, cc

      ];

       

       

      without distinct keyword:

      tab1:

      LOAD * INLINE [

          id, value

          1, a

          2, b

          3, c

          3, c

      ];


      left join(tab1)

      LOAD * INLINE [

          id, field

          1, aa

          2, bb

          3, cc

      ];

       

      Results are different here. The final table has only three records with distinct but 4 without distinct.

      My understanding is that, 2nd inline load should distinctly load data and then join with tab1, but it is not doing it instead it is taking distinct on the final resulted table.

       

      If i write the script as:

      tab1:

      LOAD * INLINE [

          id, value

          1, a

          2, b

          3, c

          3, c

      ];

      tab2:

      NoConcatenate

      LOAD distinct * INLINE [

          id, field

          1, aa

          2, bb

          3, cc

      ];

       

      left join(tab1) load * Resident tab2;

      drop table tab2;

       

      then the final table will have 4 records.

      Please help me understand the execution difference of left join with distinct in Qlikview and SQL.