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.
