Look if a string coming from Table1.field A is in Table2.field B

[Delphines]

Hi Everyone, 

I am stuck with this issue while I think it's not so complicated

I have a first table named SEARCH_FIELD containing 1 field SEARCH = the list of strings that I want to search:

SEARCH_FIELD:

SEARCH

F3

F7

F9

F20

F30

F50

I have another table containing the fiels description = a list of strings in which I want to search the above list:

TABLE:

description

mskjdf F3 msrja

mei sojfs F40msldjr c

pzorF9mzierjqm

zer.F7.mieour

there is no direct link between the 2 tables.

I would like to look for SEARCH into description and have as a result the match: 

description	match
mskjdf F3 msrja	F3
mei sojfs F40msldjr c	F40
pzorF9mzierjqm	F9
zer.F7.mieour	F7

I did it first with a Wildmatch on description: Wildmatch(description, '*F3*','*F7*','*F9*','*F20*','*F40*','*F50*'), and did the link between the numerical output and the SEARCH field. But that solution obliges me to copy the list of strings into the Wildmatch, so if I make any change, I have to do it twice, in the Wildmatch, and in the 1st table, which I wish to avoid. 

Ideally, I would like to have something like Wildmatch(description, '*'&SEARCH&'*') but that doesn't work (SEARCH is not found). I thought about using a Mapping table instead, but I don't want to replace the data in the description: only to understand if I find a string, and if yes, which one.

Thanks a lot for your help!

[Or]

Assuming there can only be one match per description,

Dimension - description

Measure:

Only(if(WildMatch(description,'*'&SEARCH&'*'),SEARCH))

Two notes - performance might be poor if your tables are large, and your example is incorrect (there's no F40 in your STRING table).

If there can be multiple matches, you'll have to switch the Only() for concat() or otherwise tell Qlik how to handle multiples, e.g. using Maxstring/Minstring.

[Delphines]

hi Or, 

this doesn't work because in the 2nd table there is no field named "SEARCH" so Qlik doesn't find it from the 1st table.

I have:

SEARCH_FIELD:

LOAD * INLINE [ SEARCH, // my list ]

NoConcatenate

TABLE:

LOAD description FROM myFile;

how can I do the Wildmatch or equivalent with 2 fields from 2 different tables without any link between them?

Thanks !

[Or]

What I posted is for doing it on the front end.

If you want to do it on the back end,

Temp:

Load STRING from Table1;

Concatenate

Load description from Table2;

Final:

Noconcatenate Load STRING, description

Resident Temp

Where Wildmatch(description,'*'&STRING&'*');

Drop table Temp;

Again, do note that if the tables are large performance might be poor as this is effectively a Cartesian join.

[Delphines]

hi Or, 

thanks again, however it doesn't work still ☹️

here we've got "F30" in the description so I would have liked to have "F30" in SEARCH

[Delphines · Accepted solution]

hi again, 

I found how to do it with a Mapping function so I put it here if someone else is trying to do the same:

SEARCH_FIELD:
MAPPING LOAD * INLINE [
TEXT_SEARCH, SEARCH
'text1', '|text1|' // adding delimiter before and after the text I'm looking for
....
];

TEMP1:
LOAD DESCRIPTION AS TEXT_SEARCH, DESCRIPTION FROM MyFile

TEMP2:
NOCONCATENATE
LOAD
MAPSUBSTRING('SEARCH_FIELD',TEXT_SEARCH) AS TEXT_RESULT,
TEXT_SEARCH,
DESCRIPTION
RESIDENT TEMP1;
DROP FIELD TEXT_SEARCH;
DROP TABLE TEMP1;

NOCONCATENATE
TEMP3:
LOAD DESCRIPTION, TEXT_RESULT, TEXTBETWEEN(TEXT_RESULT,'|','|',1) AS TEXT_MATCH RESIDENT TEMP2;
DROP TABLE TEMP2;