Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am new to Qlik Sense. I am trying to implement a logic into qlik sense but I am stuck as I don't have good knowledge in Qlik Script.
I have following tables:
Table_1:
A | B | C | D |
234 | automation technology | -600 | 4 |
289 | big data | 200 | 4 |
240 | big data | 350 | 2 |
300 | machine learning | -150 | 1 |
Table_2:
W | X | Y | Z |
web | -300 | operations | IT |
big | -200 | code | communication |
machine | -100 | develop | hub |
tech | 100 | code | communication |
I wish to join Table_2 data, to Table_1 if column [W] is substring of column [B].
Output:
A | B | C | D | W | X | Y | Z |
289 | big data | 200 | 4 | big | -200 | code | communication |
240 | big data | 350 | 2 | big | 100 | code | communication |
300 | machine learning | -150 | 1 | machine | -100 | develop | hub |
Usually, in Python we can use for loop for column [B] and inside for loop for column [W].
Solution 1: Match Substrings
Add field W to table 1
table2:
load * inline [
W,X,Y,Z
web,-300,operations,IT
big,-200,code,communication
machine,-100,develop,hub
tech,100,code,communication
];
Mapt2:
Mapping Load W,'@-'&W&'-@' Resident table2;
table1:
load *,textbetween(MapSubString('Mapt2',B),'@-','-@') as W inline [
A,B,C,D
234,automation technology,-600,4
289,big data,200,4
240,big data,350,2
300,machine learning,-150,1
];
Solution 2 : Bridge Table
Simulates a recursive search incase of One-To-Many relations
table1:
load * inline [
A,B,C,D
234,automation technology,-600,4
289,big data,200,4
240,big data,350,2
300,machine learning,-150,1
];
table2:
load * inline [
W,X,Y,Z
web,-300,operations,IT
big,-200,code,communication
machine,-100,develop,hub
tech,100,code,communication
];
testing:
load Distinct A,B Resident table1;
Left Join (testing)
load Distinct W Resident table2;
Bridge:
load A,W
Where MatchingKey>0
;
load A,index(B,W) as MatchingKey,W
Resident testing;
drop table testing;
exit Script;
Solution 1: Match Substrings
Add field W to table 1
table2:
load * inline [
W,X,Y,Z
web,-300,operations,IT
big,-200,code,communication
machine,-100,develop,hub
tech,100,code,communication
];
Mapt2:
Mapping Load W,'@-'&W&'-@' Resident table2;
table1:
load *,textbetween(MapSubString('Mapt2',B),'@-','-@') as W inline [
A,B,C,D
234,automation technology,-600,4
289,big data,200,4
240,big data,350,2
300,machine learning,-150,1
];