3 Replies Latest reply: Aug 25, 2014 10:09 AM by Jeremiah Kurpat RSS

    Problem with Left Join

      Hello,

      I need to add 2 left join to my main table. But the problem : only the first join is did, never the second.

       

      here my code :

       

      TABLE1:

      LOAD * INLINE [
      KEY, Year, Material, data1
      68, 07.2014, 111111, 100
      68, 07.2014, 222222, 101
      68, 07.2014, 333333, 102
      70, 07.2014, 444444, 500
      ]
      ;

       

      left join (TABLE1)
      LOAD * inline [
      KEY, Year, Material, data2
      70, 07.2014, 444444, 5000
      ]
      ;

      left join (TABLE1)
      LOAD * inline [
      KEY, Year, Material, data2
      68, 07.2014, 111111, 1000
      68, 07.2014, 333333, 1020
      ]
      ;

      the result must be (that I want) :

      KEY, Year, Material, data1,     data2

      68, 07.2014, 111111, 100,        1000

      68, 07.2014, 222222, 101,         -

      68, 07.2014, 333333, 102,        1020

      70, 07.2014, 444444, 500,        5000

       

      But I have :

      KEY, Year, Material, data1,     data2

      68, 07.2014, 111111, 100,        -

      68, 07.2014, 222222, 101,        -

      68, 07.2014, 333333, 102,        -

      70, 07.2014, 444444, 500,        5000

       

      or (depending of the first left)

      KEY, Year, Material, data1,     data2

      68, 07.2014, 111111, 100,        1000

      68, 07.2014, 222222, 101,         -

      68, 07.2014, 333333, 102,        1020

      70, 07.2014, 444444, 500,         -

       

      why it's not possible to do 2 left join (because I have 2 Excel files to add to my QVD). It's a bug or a QV logic ?

       

      Thanks in advance for your help.

        • Re: Problem with Left Join
          Nagaian Krishnamoorthy

          Try the following script instead:

          Table1:

          LOAD * INLINE [
          KEY, Year, Material, data1
          68, 07.2014, 111111, 100
          68, 07.2014, 222222, 101
          68, 07.2014, 333333, 102
          70, 07.2014, 444444, 500
          ]
          ;
          Table2:
          LOAD * inline [
          KEY, Year, Material, data2
          70, 07.2014, 444444, 5000
          ]
          ;
          Concatenate
          LOAD * inline [
          KEY, Year, Material, data2
          68, 07.2014, 111111, 1000
          68, 07.2014, 333333, 1020
          ]
          ;
          Left Join (Table1) LOAD * Resident Table2;
          DROP Table Table2;

           

          • Re: Problem with Left Join
            Jeremiah Kurpat

            It's because when you join the second table, the field data2 exists from the first time you joined, so therefore the join uses that new field to join onto.

             

            So you'll have this table after the first join:

            KEY, Year, Material, data1,    data2

            68, 07.2014, 111111, 100,        -

            68, 07.2014, 222222, 101,        -

            68, 07.2014, 333333, 102,        -

            70, 07.2014, 444444, 500,        5000

             

            but when you try the second join, it tries to match on the fields:

             

            KEY, Year, Material, data2

            68, 07.2014, 111111, 1000

            68, 07.2014, 333333, 1020

             

            but these records don't exist in the table, so it actually joins nothing.

             

            Hope this helps!