topic Re: How to combine two tables with multiple reference fields? in QlikView

How to combine two tables with multiple reference fields?

mrichman — Wed, 25 Nov 2020 16:16:04 GMT

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,

Re: How to combine two tables with multiple reference fields?

bramkn — Wed, 06 Jun 2018 07:19:08 GMT

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

Re: How to combine two tables with multiple reference fields?

jonathandienst — Wed, 06 Jun 2018 07:21:17 GMT

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?

Re: How to combine two tables with multiple reference fields?

mrichman — Wed, 06 Jun 2018 07:24:11 GMT

Hi Jonathan,

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

Hopefully you have a solution.

Re: How to combine two tables with multiple reference fields?

jaumecf23 — Wed, 06 Jun 2018 07:24:20 GMT

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

];

Re: How to combine two tables with multiple reference fields?

jonathandienst — Wed, 06 Jun 2018 10:56:27 GMT

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.