topic Re: Multiple Left Join on the same table in QlikView

Multiple Left Join on the same table

mdujardi — Sat, 16 Nov 2024 02:47:47 GMT

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?

thank you very much

Marc

Re: Multiple Left Join on the same table

miskinmaz — Thu, 08 Aug 2019 09:02:33 GMT

instead of left join try to use concat

Re: Multiple Left Join on the same table

marcus_sommer — Thu, 08 Aug 2019 15:12:53 GMT

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

Re: Multiple Left Join on the same table

mdujardi — Tue, 13 Aug 2019 06:58:31 GMT

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 😞

Re: Multiple Left Join on the same table

mdujardi — Tue, 13 Aug 2019 07:04:47 GMT

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.

Re: Multiple Left Join on the same table

marcus_sommer — Tue, 13 Aug 2019 07:33:35 GMT

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

Re: Multiple Left Join on the same table

mdujardi — Tue, 13 Aug 2019 07:44:29 GMT

Ok Marcus, I' ll have a look today!