Left join within if condition

[Anonymous]

Hello everyone,

I have practiced with data load editor but i am facing to JOIN trouble. There is an example:

TableA:  

ID CUS	ID BRANCH
1	00A
2	00B
3	00C
4	00D
5	00E

TableB:

ID CUS	ID BRANCH
1	00C
3	00B

I have tried Left join to create expected table like

ID CUS	ID BRANCH
1	00C
2	00B
3	00B
4	00D
5	00E

but the trouble is using left join will generate NULL value for ID BRANCH while i am tending to combine 2 tables. There is my script

TableA:

LOAD

     ID CUS as ID_CUS,

     ID BRANCH as ID_BRANCH

FROM A.xlsx;

TableB:

LOAD

     ID CUS as ID_CUS,

     ID BRANCH ID_BRANCH2

FROM B.xlsx;

LEFT JOIN (tableB)

     ID CUS as ID_CUS,

     //I have tried using alt to replace NULL value by ID_BRANCH in tableA

     //if(ID_BRANCH2 = '00C' or ID_BRANCH2='00B', ID_BRANCH2,alt())

resident tableA;

Could anyone guide me a little. Thanks in advance!

[Quy_Nguyen · Accepted solution]

Looks like you want to replace Branch in Table 1 by Branch in Table 2. Try this:

TableA:
LOAD
     ID CUS as ID_CUS,
     ID BRANCH as ID_BRANCH
FROM A.xlsx;
 Left Join
LOAD
     ID CUS as ID_CUS,
     ID BRANCH as ID_BRANCH2
FROM B.xlsx;

Result:
LOAD
     ID_CUS,
     If(ID_BRANCH2<> Null(), ID_BRANCH2,ID_BRANCH) As ID_BRANCH
Resident TableA;
Drop Table TableA;

[kaanerisen]

Hi phuc,

If your goal is that simple, you can use mapping table to achieve it.

Map_Table:

mapping load * Inline [

"ID CUS","ID BRANCH"

1,'00C'

3,'00B'

];

Master:

load

"ID CUS",

    ApplyMap('Map_Table',"ID CUS","ID BRANCH") as "ID BRANCH"

Inline [

"ID CUS","ID BRANCH"

1,'00A'

2,'00B'

3,'00C'

4,'00D'

5,'00E'

];

[Anonymous]

Thank you a lot. It works.

[Anonymous]

Thank you for helping me. Although the join above works but i kindly interested in mapping and have tried. However, the fact that, my current script is some thing like:

TableA: 

ID CUS	ID BRANCH
'='1	00A
'='2	00B
'='3	00C
'='4	00D
'='5	00E

TableB:

ID Buyer	ID Store
1	00C
3	00B

When i try to apply the mapping :

B:

Mapping LOAD

    text("ID BUYER") as BUYER,

    text("ID Store") as StoreCode

FROM B.xlsx;

A:

LOAD

Text(mid("ID CUS",4)) as BUYER,

    ApplyMap('B',BUYER,StoreCode) as "ID BRANCH"

FROM A.xlsx;

Qliksense sent out error mess "Field BUYER not found"

Could you guide me a little more.

[Quy_Nguyen]

Maybe this could help

A:

LOAD

Text(mid("ID CUS",4)) as BUYER,

    BACKUP_DATE,

    ApplyMap('B',Text(mid("ID CUS",4)),"ID BRANCH") as "ID BRANCH"

FROM A.xlsx;

[Anonymous]

Wow, many thanks, you are once of a kind. It seems like i did misunderstanding about the applymap.

[Quy_Nguyen]

You are welcome

For the error above, Qlik can not find BUYER because it doesnt exist in that context, the table hasnt been created.

If you still want to use BUYER in ApplyMap function, try preceding load:

A:

LOAD

     BUYER,

     BACKUP_DATE,

    ApplyMap('B',BUYER,"ID BRANCH") as "ID BRANCH";

LOAD

    Text(mid("ID CUS",4)) as BUYER,

    BACKUP_DATE,

    "ID BRANCH"

FROM A.xlsx;

Preceding load will help you a lot in your Qlik scripting.

[Anonymous]

That is great. Thank you very much!