Check if string contains substring across multiple categories

[Anonymous]

Hey guys, I have thousands of search phrases that I'd like to flag if the phrase contains an item from the list. Each list can have 100+ items, so coding each item into an if statement isn't ideal. If the search_phrase contains any of the substrings in the list, I need the output to reflect the name of that category. I'd then concatenate all the names of the categories where the search_phrase contains an item from the list. For example:

Strings that need flagging (from QVD file):

search_phrase
online colleges
masters in psychology
online finance degree in texas
schools that offer free laptops

The criteria lists (Excel file):

online	degree	subject	geo	general	laptop
online	associates	psychology	georgia	colleges	laptop
on line	bachelors	finance	texas	schools	tablet
on-line	masters	business administration	california	programs
	doctorate	nursing	maine	classes

Desired output:

search_phrase	flag
online colleges	online-general
masters in psychology	degree-subject
online finance classes in texas	online-subject-geo-general
schools that offer free laptops	general-laptop

I can handle the concatenating part and cleaning up the flags, but I can't figure out how to get QlikView to check if a string contains a substring within multiple lists.

Thank you,

Ben

[swuehl · Accepted solution]

Maybe like

Phrase:

LOAD search_phrase

FROM [https://community.qlik.com/thread/279459]

(html, codepage is 1252, embedded labels, table is @1);

Search:

CROSSTABLE (Topic,Term)

LOAD Recno(), online, degree, subject, geo, general, laptop

FROM [https://community.qlik.com/thread/279459]

(html, codepage is 1252, embedded labels, table is @2);

MAP:

MAPPING

LOAD Term, '//'& Topic & '\\' as Replace

Resident Search;

Result:

LOAD search_phrase, Concat(submapped, '-',subID) as result

GROUP BY search_phrase;

LOAD search_phrase, TextBetween(mapped,'//','\\',iterno()) as submapped, iterno() as subID

WHILE iterno() <= SubStringCount(mapped,'//');

LOAD search_phrase, MapSubString('MAP', search_phrase) as mapped

Resident Phrase;

DROP TABLE Phrase;

[Anonymous]

Incredible, it worked! Thank you very much for the quick response. I hadn't used most of those functions before, but I will acquaint myself with them now. Do you mind explaining how that Result table works though?

[swuehl]

Ben,

Have a look (e.g. in a table box) at the Search table transformed using CROSSTABLE LOAD prefix:

The Crosstable Load

Then I've created a MAPPING table

Don't join - use Applymap instead

but used the Mapsubstring() function in the Result Table LOAD to replace all ocurrences of the search Terms with a //Topic\\ code.

MapSubstring ‒ QlikView

The Result table preceding LOADs need to be read bottom to top, so the LOAD with the MapSubstring() is done first.

Comment out the upper two LOAD statements and reload to see the mapped results.

The second LOAD from the bottom now creates a record per found //Topic\\ code (use other delimiters than // and \\ if these may conflict with your searched content) using a WHILE loop.

Loops in the Script

Load ‒ QlikView (see the WHILE clause)

Comment out only the topmost LOAD statement to have a look at the single records.

The last (topmost) LOAD then aggregates the records per search_phrase and creates a concatenated string for the found Topic values.

[Anonymous]

Ok I understand now. Thank you for helping me out!