Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
mrichman
Creator II
Creator II

How to combine two tables with multiple reference fields?

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,

1 Solution

Accepted Solutions
jaumecf23
Creator III
Creator III

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

];

View solution in original post

5 Replies
bramkn
Partner
Partner

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?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
mrichman
Creator II
Creator II

Hi Jonathan,

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

Hopefully you have a solution.

jaumecf23
Creator III
Creator III

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

];

View solution in original post

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein