topic Re: Question about the "If(Wildmatch)" function in QlikView

Question about the "If(Wildmatch)" function

MEJ — Wed, 25 Nov 2020 16:16:04 GMT

I often use the wildmatch or match function in the load script or within reports.

However, some of my wildmatch scripts tend to be extremely long, which makes it cumbersome to work with (both in terms of finding the script and it takes long to scroll past these rows). I would much rather work with mapping tables as they are easier to get an overview with, and maintain, plus other people can maintain that table outside of Qlikview.

For example

if(Wildmatch([Configured Item Description],'* ABC *' , 'AC1') or Wildmatch([Bill To Customer], '* ABC *') or Wildmatch([Final Customer], '* ABC *'), 'ABC') & if(Wildmatch([Configured Item Description],'* DEF *') or Wildmatch([Bill To Customer], '* DEF *') or Wildmatch([Final Customer], '* DEF *', '* DF *'), 'DEF') as Customer

These are two criteria for two customers, but the problem is that I have about 100 customers that I need to create a common customer name for. If the customer name ABC (with preceding space and ending with a space) is in either these three fields, I would like to return the value ABC.

How is it possible to use wildcards using a mapping table in Excel instead?

Thanks

Re: Question about the "If(Wildmatch)" function

tresB — Wed, 19 Jun 2019 07:00:28 GMT

I am not jumping into the mapping solution you are looking for here. One quick easier approach appears to me is - concatenate all the fields into one and then search for your string, because anyway you are looking for one string pattern in all fields with OR condition. Hence, may be you can try like:

If ( WildMatch( Field1 &'|'&Field2 &'|'&Field3 , '* ABC *'), 'ABC') as Customer

Re: Question about the "If(Wildmatch)" function

MEJ — Wed, 19 Jun 2019 07:02:07 GMT

Thanks, that's good to know. I edited my question somewhat. Not always do I have the same criteria in all 3 fields, therefore it wouldn't be applicable in this instance, but surely useful for other purposes.

Re: Question about the "If(Wildmatch)" function

tresB — Wed, 19 Jun 2019 07:07:59 GMT

Probably MapSubstring() could be helpful here.