3 Replies Latest reply: Mar 16, 2017 4:41 AM by Enzo Schwander RSS

    Left Join

    Enzo Schwander

      Hi there,

       

      I spend a lot of time looking for a solution to enrich a table but I cannot do what I want.

       

      Here is what I Made:

       

      Table1:

      LOAD

      MyID,

      Task;

      FROM [MyLib/Table1.xlsx]

      (ooxml, embedded labels, header is 189, table is Request);

       

      Left Join

      LOAD

      [MyID],

      [MyFlag];

      LOAD

      [MyID],

      1 AS [MyFlag]

      FROM [MyLib/Table2.xlsx]

      (ooxml, embedded labels, header is 24, table is Request);

       

      Table1 looks like this:

       

      MyID     |     Task         

      AAA      |        1                 

      AAA      |        2                

      AAA      |        3                

      BBB      |        1                

      BBB      |        2                 

      CCC      |        1               

      DDD      |        1

       

      Table2 looks like this:

       

      MyID     |     Task      |       MyFlag

      AAA      |        2         |            1

      BCD      |        1         |           1

      BBB      |        2         |            1

      ZZZ      |        1         |           1

      XSD      |        1         |          1

       

       

      And I have this result:

       

      MyID     |     Task      |       MyFlag

      AAA      |        1         |           null

      AAA      |        2         |             1

      AAA      |        3         |           null

      BBB      |        1         |           null

      BBB      |        2         |             1

      CCC      |        1         |           null

      DDD      |        1         |           null

       

      And I want to have:

       

      MyID     |     Task      |       MyFlag

      AAA      |        1         |             1

      AAA      |        2         |             1

      AAA      |        3         |             1

      BBB      |        1         |             1

      BBB      |        2         |             1

      CCC      |        1         |           null

      DDD      |        1         |           null

       

      And I do not understand why it does not work because I did not put any restriction/filter on Task Code...

       

      Thanks a lot for your help

       

      Cheers

        • Re: Left Join
          Sunny Talwar

          May be you don't want to join on Task and only on MyID

           

          Table1:

          LOAD * INLINE [

              MyID, Task

              AAA, 1

              AAA, 2

              AAA, 3

              BBB, 1

              BBB, 2

              CCC, 1

              DDD, 1

          ];

           

          Left Join(Table1)

          LOAD MyID,

            MyFlag,

            Task as Task_Temp;

          LOAD * INLINE [

              MyID, Task, MyFlag

              AAA, 2, 1

              BCD, 1, 1

              BBB, 2, 1

              ZZZ, 1, 1

              XSD, 1, 1

          ];

           

          Capture.PNG

            • Re: Left Join
              Enzo Schwander

              Hey! thanks for replying.

               

              In fact this was just an example I am loading big tables from excel files and I can't copy them directly in the code XD.

               

              And if you look at my code I am not joinning on Tasks... that's why I am a bit perplex... I am joinning only on 'MyID'

               

              Cheers

            • Re: Left Join
              Enzo Schwander

              I find a way! in fact I was not using right field naming.

               

              Thanks for help