Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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?
thank you very much
Marc
instead of left join try to use concat
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.
- Marcus
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.
- Marcus
Ok Marcus, I' ll have a look today!