Qlik Community

Suggest an Idea

Vote for your favorite Qlik product ideas and add your own suggestions.

Search a list of patterns in a data set

gurudattanbhat
Partner
Partner

Search a list of patterns in a data set

Problem statement: How do I look for a list of keywords or phrases in a particular field in a transaction level data set.

1. List of keywords (below mapping load)

2. Transaction data set

Suggestion:

KeywordsTable:
mapping Load
lower([Field1_Keywoard_List]),
Status
FROM [lib://Table/Keywords.xlsx]
(ooxml, embedded labels, table is [Sheet1]);

TransactionData:
LOAD
     Field2_Description,

     if(substringcount(MapSubString('KeywordsTable',lower(Field2_Description)),'Match')>0,'Match,'') as Field3_Status,

FROM [lib://Table/TransactionLevelData.xlsx]
(ooxml, embedded labels, table is Sheet1);

 

The essence here is using MapSubString one can fine multiple keywords or phrases while loading data from the transaction level dataset. The function returns a numeric value which reflects how many times the key word(s) appeared, if it is more than 0 then at least once there was a match hence update the status to reflect a match.

2 Comments
John_Teichman
Employee
Employee
I am not sure what the Idea / request is. Can you clarify your request?
John_Teichman
Employee
Employee

Closing this idea as the requested user feedback was not received.

Status changed to: Closed - Archived