Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Left join within if condition

Hello everyone,

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

TableA:  

ID CUSID BRANCH
100A
200B
300C
400D
500E

TableB:

  

ID CUSID BRANCH
100C
300B

I have tried Left join to create expected table like

  

ID CUSID BRANCH
100C
200B
300B
400D
500E

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!

1 Solution

Accepted Solutions
Quy_Nguyen
Specialist
Specialist

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;

View solution in original post

8 Replies
Quy_Nguyen
Specialist
Specialist

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
Creator III
Creator III

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'

];

Untitled.png

Anonymous
Not applicable
Author

Thank you a lot. It works.

Anonymous
Not applicable
Author

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 CUSID BRANCH
'='100A
'='200B
'='300C
'='400D
'='500E

TableB:

ID BuyerID Store
100C
300B

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
Specialist
Specialist

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
Not applicable
Author

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

Quy_Nguyen
Specialist
Specialist

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
Not applicable
Author

That is great. Thank you very much!