Discussion Board for collaboration on QlikView Scripting.
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.
If(Name Like '*IT*' and NOT Wildmatch(Name,'*'&ApplyMap('ITExceptions',Name)&'*')> 0, 1,0 ) as ITExceptions,
Go to 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*';
If(Name Like '*IT*' and Wildmatch(Name, $(vExclude)) = 0, 1,0 ) as ITExceptions;
LOAD * INLINE [
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 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
Let vExclude = peek('Exclude',0);
Thanks! I give this a try.