Left Join in Data Load Editor not working

[Anonymous]

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:

Then I have a 2nd table called Operations:

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:

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

[sunny_talwar · Accepted solution]

Where is ID_CUSTOMER field in your Resident table? Also, why you have resident from clientes and not Customer Table? May be try the below

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 ID_CUSTOMER

NAME as i_name,

AGE as i_age,

HEIGHT as i_height,

PROFESSION as i_profession,

NATIONALITY as i_nationality

Resident customers;

[vinieme12]

the field names must be same in both tables to perform a JOIN, you are missing the common field to perform the JOIN on

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

ID_CUSTOMER

NAME as i_name,

AGE as i_age,

HEIGHT as i_height,

PROFESSION as i_profession,

NATIONALITY as i_nationality

Resident clientes;

[Anonymous]

Hi Sunny, thank you very much for your answer. It is correct!

Table "clientes" was just a typo, and I changed it to "Customers" while you were answering my question; as this is a translation of an analogous example. Thanks a lot.

[Anonymous]

Hi Vineeth, thank you for your response.

Totally right. I must have added column ID_CUSTOMER to get the results right. But bear in mind that I changed my example and removed table "clientes" as it was a typo. Thanks in advance.