topic Re: Combine Wildmatch and Applymap for string comparisons in QlikView

Combine Wildmatch and Applymap for string comparisons

b_garside — Tue, 06 May 2014 16:55:30 GMT

Im trying to tag or flag a Name field that has 'IT' but not certain letters that are in a word.

My expression pulls forma mapping list to determine if a title has some excluded words in it. I thought I could use applymap vs listing all of them inline.

Current which works but not efficient.

if(Name Like '*IT*' and Not Name like '*iti*' and Not Name Like '*ito*' and Not Name Like '*itu*'
and Not Name Like '*ity*' and Not Name Like '*ita*' and Not Name Like '*its*' and Not Name Like '*ite*'
and Not Name Like '*ith*' and Not Name Like '*itt*' and Not Name Like '*itr*' and Not Name Like '*it)*' , 1 , 0) as DQ_IT_Name_Flg,

The problem is Applymap is not able to compare the Name string properly against the Mapping table since its only has the 3 digit strings where name is much longer. Applymap can't interpret a wildcard.

Proposed logic.

If(Name Like '*IT*' and NOT Wildmatch(Name,'*'&ApplyMap('ITExceptions',Name)&'*')> 0, 1,0 ) as ITExceptions,

Exclusions

String	Output
iti'	iti'
ito'	ito'
itu'	itu'
ity'	ity'
ita'	ita'
its'	its'
ite'	ite'
ith'	ith'
itt'	itt'
itr'	itr'
it)'	it)'
fit'	fit'
dit'	dit'
nit'	nit'

Re: Combine Wildmatch and Applymap for string searches

swuehl — Tue, 06 May 2014 17:11:30 GMT

If you have some few exclusions, define your exclusions in a string variable and then use the variable in wildmatch:

Set vExclude = '*iti*' , '*ito*';

LOAD *,

If(Name Like '*IT*' and Wildmatch(Name, $(vExclude)) = 0, 1,0 ) as ITExceptions;

LOAD * INLINE [

Name

ITiti

ITito

];

Re: Combine Wildmatch and Applymap for string comparisons

b_garside — Tue, 06 May 2014 17:17:48 GMT

I have 14 exclusions and growing. This method will work though.

I guess the reason I wanted to use applymap is so I could add to the list in Excel and have it use that.

Re: Combine Wildmatch and Applymap for string comparisons

Tue, 06 May 2014 17:25:22 GMT

Just another suggestion. you can try using mapsubstring() like in attached file if you have a very lengthy list.

Hope it helps

thanks

Re: Combine Wildmatch and Applymap for string comparisons

b_garside — Tue, 06 May 2014 17:31:33 GMT

Thanks I will try that too.

Re: Combine Wildmatch and Applymap for string comparisons

swuehl — Tue, 06 May 2014 20:33:20 GMT

Loading the exclude list from a table is also possible:

LOAD chr(39)&Concat(Exclude,chr(39)&','&chr(39) )&chr(39) as Exclude

INLINE [

Exclude

*iti*

*ito*

];

Let vExclude = peek('Exclude',0);

LOAD *,

If(Name Like '*IT*' and Wildmatch(Name, $(vExclude)) = 0, 1,0 ) as ITExceptions;

Re: Combine Wildmatch and Applymap for string comparisons

b_garside — Wed, 07 May 2014 15:21:30 GMT

Thanks! I give this a try.