Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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.
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 !
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.
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
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;