topic Re: Joining 2 Fact tables on multiple keys based on Condition in QlikView

Joining 2 Fact tables on multiple keys based on Condition

Anonymous — Mon, 03 Apr 2017 14:43:29 GMT

Solution: (Conditional Join)

Link Table 1 (Key1) to Table 2 (Key1) if they exist

Else link Table 1 (Key2) to Table 2 (Key2)

Else "NULL".

How do i approach this solution, and build the linking key ?

Any help is appreciated.

Thanks,

Re: Joining 2 Fact tables on multiple keys based on Condition

ahaahaaha — Mon, 03 Apr 2017 14:58:32 GMT

Hi Abhay,

Can still a fragment of the original data and what do you want to get at the output?

Regards,

Andrey

Re: Joining 2 Fact tables on multiple keys based on Condition

CarlosAMonroy — Mon, 03 Apr 2017 15:05:24 GMT

Hi Abhay,

Maybe try something like this:

LinkTable:

Load if(len(Key1)>0,Key1,Key2) as Key, Key1 as T1.Key1, Key2 as T1.Key2, Field

From Table1;

join(LinkTable)

Load if(len(Key1)>0,Key1,Key2) as Key, Key1 as T2.Key1, Key2 as T2.Key2, Field

From Table2;

Hope that helps,

Carlos M

Re: Joining 2 Fact tables on multiple keys based on Condition

aarkay29 — Mon, 03 Apr 2017 15:07:42 GMT

May be something like this :

load * Inline [

A.ID1,A.ID2,Value

1,4,45

2,5,50

3,6,60

];

load * Inline [

B.ID1,B.ID2,Product

1,,A

2,,B

3,5,C

,4,D,

1,4,G

];

Left Join (A)

Load

B.ID1 as A.ID1,

Product

Resident

Join (A)

Load

B.ID2 as A.ID2,

Product

Resident

Where

Not exists(A.ID1,B.ID1)

;

Drop Table B;