Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

phucpv9119
New Contributor II

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

Re: Left join within if condition

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;

8 Replies
Quy_Nguyen
Contributor III

Re: Left join within if condition

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

Re: Left join within if condition

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

phucpv9119
New Contributor II

Re: Left join within if condition

Thank you a lot. It works.

phucpv9119
New Contributor II

Re: Left join within if condition

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

Re: Left join within if condition

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;

phucpv9119
New Contributor II

Re: Left join within if condition

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

Quy_Nguyen
Contributor III

Re: Left join within if condition

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.

phucpv9119
New Contributor II

Re: Left join within if condition

That is great. Thank you very much!

Community Browser