Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
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
Partner
Partner

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

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
Creator II
Creator II

Hi Jonathan,

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

Hopefully you have a solution.

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

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