Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I have a requirement that need to join two tables twice by different field. Below is the scenario
Table A
Field 1 | Field 2 | Field 3 |
---|---|---|
1 | F21 | |
2 | F32 | |
3 | F23 | |
4 | F34 |
Table B
Field 4 | Field 5 | Field 6 |
---|---|---|
A | F21 | |
B | F22 | F32 |
C | F23 | |
D | F34 |
Table A Field 3 left join Table B field 6
Result of first Join
Field 1 | Field 2 | Field 3 | Field 4 | Field 5 |
---|---|---|---|---|
1 | F21 | |||
2 | F32 | B | F22 | |
3 | F23 | |||
4 | F34 | D |
Table A Field 2 left join Table B field 5
Result of second Join
Field 1 | Field 2 | Field 3 | Field 4 | Field 5 | Field 6 |
---|---|---|---|---|---|
1 | F21 | A | |||
2 | F32 | B | F22 | ||
3 | F23 | C | |||
4 | F34 | D |
How can i do the above joining in loading script?
Best Regards,
Louis
Use Left join once and use Applymap() the second time
Example
Load
temp:
Field1,Field2,Field3
From Table1Src;
Left Join
Field4,Field5,Field6 as Field3
From Table2Src
Map_Field4:
Mapping Load Distinct Field5,
Field4
From Table2Src;
NOCONCATENATE
FACT:
LOAD
Field1,
Field2,
Field3,
if(isnull(Field4),Applymap('Map_Field4',Field5),Field4) as Field4
Field5
RESIDENT temp;
Drop Table temp;
give this a read
Don't join - use Applymap instead
A:
LOAD [Field 1],
if(len(trim([Field 2]))=0, null(), [Field 2]) as [Field 2],
if(len(trim([Field 3]))=0, null(), [Field 3]) as [Field 3]
FROM [https://community.qlik.com/thread/248886] (html, codepage is 1252, embedded labels, table is @1);
Join (A)
LOAD [Field 4],
if(len(trim([Field 5]))=0, null(), [Field 5]) as [Field 5],
if(len(trim([Field 6]))=0, null(), [Field 6]) as [Field 6]
FROM [https://community.qlik.com/thread/248886] (html, codepage is 1252, embedded labels, table is @2);
B:
NoConcatenate LOAD *
Resident A
Where [Field 2]=[Field 5] or [Field 3]=[Field 6];
DROP Table A;
Hi Massimo,
Can you provide the .qvw for reference?
Best Regards,
Louis
yes but I've already posted all the script