Hello everyone, I have been struggeling for a few hours now on a left join problem and i´d like your help.
I have 3 tables sharing a key called PK_CHAT (I am using chatbots data)
The first table table 1 only have one row per PK_CHAT
The second one table 2 can have multilpe rows per PK_CHAT
The third one table 3 can have multilpe rows per PK_CHAT
I want to do 2 left join using table 1 as my master table and link table 1 to table 2 and table 1 to table 3. However I don´t want table 2 and table 3 to be directly related. Let me explain with an example.
Table 1
PK_CHAT
Variable X
PK 1
A
Table 2
PK_CHAT
Variable Y
PK 1
B
PK 1
C
Table 3
PK_CHAT
Variable Z
PK 1
D
I´d like the result to be
PK_CHAT
Variable X
Variable Y
Variable Z
PK 1
A
B
D
PK 1
A
C
But I get the following result doing two left join in qlikse >> Left join (table 1) LOAD ....
PK_CHAT
Variable X
Variable Y
Variable Z
PK 1
A
B
D
PK 1
A
C
D
Do you know if my problem has a solution or should I try to find another data structure?
I think it won't be possible with one step but with some additionally work like:
t1: load PK_CHAT, X from table1; left join load PK_CHAT, concat(Y, ',') as Y, count(Y) as Ycount from table2 group by PK_CHAT; left join load PK_CHAT, Z from table3;
t2: load PK_CHAT, X, if(Ycount = 1, Y, subfield(Y, ',', iterno())) as Y, if(iterno()=1, Z) as Z resident t1 while iterno() <= substringcount(Y, ',') + 1
you could create such a data-structure.
But there might be better approaches than that, for example you just associate these tables within the datamodel without any merging (maybe only partly) or you concatenate them.
Hi Miskinmaz, thank you. I had a look at concatenate but they say "Concatenate is very helpful when you have to include 2 or more Fact tables in your data model. These Fact tables should be of same granularity" . In my case I don´t have the same data structure for the 2 tables 😞
Hi Marcus, thank you. really smart code but as you said it´s start to be too complicated. I should develop another approach. I think I will load them separately.
Asynchronous concatenated tables and/or tables which contain data with a different granularity are possible and work often very well. Beside this it's usually much more easier to develop a datamodel with this approach as all other ways - therefore you shouldn't discard it too fast.
