1 Reply Latest reply: Sep 23, 2013 4:37 PM by Ralf Becher RSS

    Joins not working in loop

      Hello,

       

      I need to load many files in the application and i have used a loop to achieve it. i have taken an example with only 2 files(xls) to do the test. In each files i have 2 tables (Sheet1 and sheet2) which i wish to join using left join.

      The issue that i am encountering is that line of 1 data is not present in the final result set (where e=20). see below:

      Expected result:

      abcde
      123412
      5678-
      1011121320
      14151617-

       

       

      Below is my join:

       

      FOR x =0 to 1   

       

      if $(x) =0 then

      //

      TRACe 1st;

         

          //NoConcatenate

          CORP:

          LOAD *

          FROM

          [C:\Projects\Ava\Ava Ops\Test.xls]

          (biff, embedded labels, table is [Sheet1$]);

       

          left Join(CORP)

          LOAD *

          FROM

          [C:\Projects\Ava\Ava Ops\Test.xls]

          (biff, embedded labels, table is [Sheet2$]);

       

       

      ELSE

       

      TRACe 2nd;

       

          Concatenate(CORP)       

          LOAD a,

           b,

           c,

           d

          FROM

          [C:\Projects\Ava\Ava Ops\Test2.xls]

          (biff, embedded labels, table is [Sheet1$]);

       

          left join(CORP)

          LOAD

           c,

           d,

           e

          FROM

          [C:\Projects\Ava\Ava Ops\Test2.xls]

          (biff, embedded labels, table is [Sheet2$]);

         

      ENDIF

         

      NEXT x

       

      Attached you will the excel files which i used as tables.

       

      Can anyone help please?

        • Re: Joins not working in loop
          Ralf Becher

          Hi Viju,

           

          the 2nd join will fail because the target table already has those columns. One solution is to load all tables concatenated and do a final join at the end:

           

          FOR x =0 to 1  
          
          if $(x) =0 then
              //NoConcatenate
              CORP:
              LOAD *
              FROM
              [D:\Test.xls]
              (biff, embedded labels, table is [Sheet1$]);
          
              TO_JOIN:
              LOAD *
              FROM
              [D:\Test.xls]
              (biff, embedded labels, table is [Sheet2$]);
          ELSE
              Concatenate(CORP)      
              LOAD a,
               b,
               c,
               d
              FROM
              [D:\Test2.xls]
              (biff, embedded labels, table is [Sheet1$]);
          
              Concatenate(TO_JOIN)
              LOAD
               c,
               d,
               e
              FROM
              [D:\Test2.xls]
              (biff, embedded labels, table is [Sheet2$]);
          
          ENDIF
          
          NEXT x
          
          left join(CORP)
          LOAD * Resident TO_JOIN;
          
          drop table TO_JOIN;
          
          
          
          
          
          
          
          
          

           

          - Ralf