Combine Wildmatch and Applymap for string comparisons

[b_garside]

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*'

[swuehl · Accepted solution]

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]

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. 

[#4]

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]

Thanks I will try that too.

[swuehl]

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]

Thanks! I give this a try.