4 Replies Latest reply: Jan 16, 2018 9:27 AM by Anibal Martinez-Sistac RSS

    Left Join in Data Load Editor not working

    Anibal Martinez-Sistac

      Hi Community. What I am trying to achieve is a simple Left Join. Nevertheless, I am not getting the results I am expecting.

       

      I have a table called Customers:

      table a.png

      Then I have a 2nd table called Operations:

      tableb.png

      What I want to create is a new table Results left join so that I can have information of registries of table Customers whose ID_CUSTOMER also appears in the table Operations as ID_BUYER. Something like:

       

      tablec.png

      I have tried the following code in Data Load editor without success:

      LET vData = 'lib://data';

       

      customers:

      load *

      From '$(vData)/customers.csv';

       

      operations:

      load *,

      ID_BUYER AS ID_CUSTOMER

      From '$(vData)/operations.csv';

      DROP FIELD ID_BUYER;

       

      Left Join (operations)

      Load

      NAME as i_name,

      AGE as i_age,

      HEIGHT as i_height,

      PROFESSION as i_profession,

      NATIONALITY as i_nationality

      Resident customers;

       

      The result of what I am getting is a cartesian product of my desired "Left join" table with 12 fields and 30 rows instead of 5 rows with 12 fields as it can be seen in my third image. I know I have a missing statement or condition. Any help is highly appreciated. Thanks, Anibal