Combining 2 tables : key is either one field either another one

[Boulanger]

Hi everyone,

As an image equals many words I've attached to this thread my current struggle.

I've done a lot of test and "workaround" (which means doing and stacking things I don't master and conclude it doesn't work)

I've tried join, keep, concatenate, 2-steps join (table1.B - table2.D, table1.C - table2.D) but nothing return me the result I want, my lines keep being duplicated.

Could you help me resolve this which would also help me get a better understanding  of table combination in Qlik ?

Note : 

- I want to keep all the lines from table 1 AND table 2

Here is the code for loading these dummy tables if you want! 🙂

Spoiler

NoConcatenate
Table1:
Load *
inline
[
A,B,C
1, 123, FRA0101, 11111
2, 124, '-', 22222
3, 125, FRA0202, 33333
4, 126, '-', 44444
5, 127, '-', 55555
6, 128, FRA0303, 66666
];

NoConcatenate
Table2:
Load *
inline
[
D, E, F
11111, abc, rty
22222, fgh, jkl
FRA0202, dfv, ghj
99999, qde, dio
55555, qsd, sdf
FRA0303, dfg, nhy
66666, gta, tya
77777, erc, avx
FRA6666, yxs, pgq
];

NoConcatenate
ResultTableExpected:
Load *
inline
[
Aa, Dd, Ee, Ff
123, 11111, abc, rty
124, 22222, fgh, jkl
125, FRA0202, dfv, ghj
126, 99999, qde, dio
127, 55555, qsd, sdf
128, FRA0303, dfg, nhy
'-', 66666, gta, tya
'-', 77777, erc, avx
'-', FRA6666, yxs, pgq
];

Many thanks to the community, I've learned a lot by reading you ! 🙂

Qlik Cloud 

[rubenmarin]

Hi, this script is close, I don't know why in your table expected D=99999 is assigned to B=126.

Spoiler

Table1:
Load * inline
[
A,B,C,D
1, 123, FRA0101, 11111
2, 124, '-', 22222
3, 125, FRA0202, 33333
4, 126, '-', 44444
5, 127, '-', 55555
6, 128, FRA0303, 66666
];

Table2:
Load * inline
[
E, F, G
11111, abc, rty
22222, fgh, jkl
FRA0202, dfv, ghj
99999, qde, dio
55555, qsd, sdf
FRA0303, dfg, nhy
66666, gta, tya
77777, erc, avx
FRA6666, yxs, pgq
];

// Load values joined by C
CJoinValues:
NoConcatenate
LOAD B,C as D, C as Key, B as BLoaded Resident Table1;
Inner Join LOAD E, E as Key, F, G, E as ELoaded Resident Table2;

// Load values joined by D not already joined by C
DJoinValues:
LOAD B,D, D as Key, B as BLoaded Resident Table1 Where not exists('BLoaded',B);
Inner Join LOAD E, E as Key, F, G, E as ELoaded Resident Table2;

FinalTable:
NoConcatenate LOAD * Resident DJoinValues;
Concatenate LOAD * Resident CJoinValues;
Concatenate LOAD E, F, G Resident Table2 Where not Exists('ELoaded', E);

DROP Tables Table1, Table2, DJoinValues, CJoinValues;

Some letters has changed because the first table has 4 columns

[Boulanger]

Hi! Thanks for your reply

You're right I introduced a mistake in my manual "expected table" ; in this case it wouldn't be matched. 

I'm still getting duplicated lines with the approach of mapping two time table 1 and table C : one join *2 columns that are the keys. (resultTable : Table1.B join Table2.D join Table1.C..)

[rubenmarin]

Hi, can you post wich values are duplicated? The Exists() clause should prevent duplicated.