Left join a table twice by specific field

[Anonymous]

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

[vinieme12]

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;

[vinieme12]

give this a read

Don't join - use Applymap instead

[maxgro]

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;

[Anonymous]

Hi Massimo,

Can you provide the .qvw for reference?

Best Regards,

Louis

[maxgro]

yes but I've already posted all the script