Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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! 🙂
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 ! 🙂
Hi, this script is close, I don't know why in your table expected D=99999 is assigned to B=126.
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
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..)
Hi, can you post wich values are duplicated? The Exists() clause should prevent duplicated.