Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;
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'
];
Thank you a lot. It works.
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.
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;
Wow, many thanks, you are once of a kind. It seems like i did misunderstanding about the applymap.
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.
That is great. Thank you very much!