Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

b_garside
Valued Contributor

Combine Wildmatch and Applymap for string comparisons

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

StringOutput
*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*'
1 Solution

Accepted Solutions
MVP
MVP

Re: Combine Wildmatch and Applymap for string searches

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

IT

ITiti

ITito

];

6 Replies
MVP
MVP

Re: Combine Wildmatch and Applymap for string searches

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

IT

ITiti

ITito

];

b_garside
Valued Contributor

Re: Combine Wildmatch and Applymap for string comparisons

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. 

Not applicable

Re: Combine Wildmatch and Applymap for string comparisons

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

Hope it helps

thanks

AJ

b_garside
Valued Contributor

Re: Combine Wildmatch and Applymap for string comparisons

Thanks I will try that too.

MVP
MVP

Re: Combine Wildmatch and Applymap for string comparisons

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;

b_garside
Valued Contributor

Re: Combine Wildmatch and Applymap for string comparisons

Thanks! I give this a try.

Community Browser