How to perform for inside for loop with string matching in Data Editor?

[omkar_2611]

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].

[MarcoWedel]

maybe this helps:

Grouping List Box values

Match string to substring from another table

if condition

Keyword Mapping with Description

Multi-wildmatch on Text

How load necessary information from unstructured column?

[vinieme12]

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;

[vinieme12 · Accepted solution]

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
];