6 Replies Latest reply: May 7, 2014 11:21 AM by Brian Garside RSS

    Combine Wildmatch and Applymap for string comparisons

    Brian 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

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