How to match the list of substrings with a field and map the corresponding value

[MadhumithaP]

Hi All,

I have a requirement like below, there are list of substring values. Each substring value belongs to each category. Without using the if..else.. condition how to achieve this.

In below example, I have a field called comment, using this field I have a list of substring's to be checked(in the below table). If the comment contains 'Ping ID' or 'pingid', then it should come under 'Ping ID Issues' Category and so on.

Could you please help in achieving this without using if else condition. Because i have around 50+ substring's to be checked (Imagine I have all those in an excel)

Comment

I have a Ping ID issue. Due to this I am not able to login to the server

choose a Board and ask away

Unable to reset the password in the tool

I have a Ping ID issue. Due to this issue, the server login is failing

substring's to check	Category
Ping ID', 'pingid'	Ping ID Issues
Board'	Board Issues
password'	Password Issues

[Vegar]

You can use an pick-match technique to do this.

I usually let Rob Wuderlich do the job for me when I need to do this in my projects.

Rob has written a SUB library, QlikView Components, that he started with for about 10yrs ago. It may be old and it may be written for QlikView, bit it is still a good resource to use today in a Qlik Sense setting.

Check out the Qvc_wildmap SUB: https://github.com/RobWunderlich/Qlikview-Components/blob/master/QVC_Source/Qvc_WildMap.qvs

[tresesco · Accepted solution]

Try like:

Map:
Mapping LOAD 
     Trim(SubField(Purgechar([substring's to check], chr(39)), ',')) as Substring,
     '|'&Category&'|'
FROM
[https://community.qlik.com/t5/App-Development/How-to-match-the-list-of-substrings-with-a-field-and-map-the/td-p/2011849]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @2);


t2:	
LOAD Comment,
	 TextBetween( MapSubString('Map',Comment), '|', '|') as MappedCategory
FROM
[https://community.qlik.com/t5/App-Development/How-to-match-the-list-of-substrings-with-a-field-and-map-the/td-p/2011849]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);