Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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*' |
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
];
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
];
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.
Just another suggestion. you can try using mapsubstring() like in attached file if you have a very lengthy list.
Hope it helps
thanks
AJ
Thanks I will try that too.
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;
Thanks! I give this a try.