Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
bgoldberg
Contributor III
Contributor III

Check if string contains substring across multiple categories

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):

onlinedegreesubjectgeogenerallaptop
onlineassociatespsychologygeorgiacollegeslaptop
on line

bachelors

financetexasschoolstablet
on-linemastersbusiness administrationcaliforniaprograms
doctoratenursingmaineclasses

Desired output:

search_phraseflag
online collegesonline-general
masters in psychologydegree-subject
online finance classes in texasonline-subject-geo-general
schools that offer free laptopsgeneral-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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

4 Replies
swuehl
MVP
MVP

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;

View solution in original post

bgoldberg
Contributor III
Contributor III
Author

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
MVP
MVP

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.

bgoldberg
Contributor III
Contributor III
Author

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