How to combine two tables with multiple reference fields?

[mrichman]

Hi Community,

I was wondering on how you would solve this issue... We have two tables which we would like to left join but avoid Sync error key.

We are trying to left join them on "Refnr" but as there are no further rules, the data just explodes. This is because adds the ID on all refnr but we only want data to be joined where applicable. Please see Current Result and Ideal Result.

Hopefully you guys can help on what the best approach would be, thanks in advance.

We have two tables with the following information:

Table 1:

LOAD * inline [

Refnr,Size

123, S,

123, M,

123, L,

] (delimiter is '|');

Table 2:

LOAD * inline [

Refnr, ID, Size, Inventory

123, A, S, 50

] (delimiter is '|');

Current result when left joining:

Refnr,Size, ID,Inventory

123, S, A, 50

123, M, A, 50

123, L, A, 50

Ideal result after left joining:

Refnr,Size, ID,Inventory

123, S, A, 50

123, M,

123, L,

[bramkn]

This should be going fine if Refnr and Size have the exact same field names. Your results are missing a field name.

[jonathandienst]

Your examples are inconsistent. The field list has four fields, but you show values for 5 fields. Where does the value of 43 come from?

[mrichman]

Hi Jonathan,

Thank you for your reply. Example has been edited apologies.

Hopefully you have a solution.

[jaumecf23 · Accepted solution]

Hi,

Try something like this:

Table1:

Load *,

Refnr&'#'&Size as KEY;

LOAD * inline [

Refnr,Size

123, S

123, M

123, L

];

Table2:

left join(Table1)

Load ID,

     Inventory,

Refnr&'#'&Size as KEY;

LOAD * inline [

Refnr, ID, Size, Inventory

123, A, S, 50

];

[jonathandienst]

You have (delimiter is '|'); as a qualifier, so the only field being populated is the first. Remove that from both inlines and your script works perfectly.