2 Replies Latest reply: Jul 29, 2009 8:21 PM by Shumail Hussain RSS

    SQL "Like" in QlikView

    Shumail Hussain

      Hi,

      Can we use like keyword in QlikView? and also what is the way to update the value of fields?

       


      UPDATE[cardmast] SET [Credit_Limit] = 0, Cur_Bal= 0 WHERE [Card_Scheme]=3AND ACCT_STUP_FLAG='j' AND (AMED_USER_2 Like '%mul%' Or AMED_USER_2Like 'sup%')


      I was trying index function for this but i cudn't get the correct result.

       


      CARDMAST:
      LOAD IF(index('j',ACCT_STUP_FLAG) and Card_Scheme=3 AND(INDEX('MUL',AMED_USER_2) or INDEX('SUP',AMED_USER_2)),'0', CUR_BAL) ASCUR_BAL RESIDENT CDBS


      Regards,
      SHUMAIL HUSSAIN

        • SQL "Like" in QlikView

          Hi, you can use the function wildmatch(), with thw wildcards '*' for more than 1 character, or the '?' for one, this is an example script and table

          t1:
          LOAD
          F1,
          if(wildmatch(F1,'sup*'),'True','False') as like_1,
          if(wildmatch(F1,'*sup'),'True','False') as like_2,
          if(wildmatch(F1,'*sup*'),'True','False') as like_3,
          if(wildmatch(F1,'?at?up'),'True','False') as like_4
          INLINE [
          F1
          Sup
          sup
          superi
          Catsup
          Catzup
          ];
          <table><tbody><tr> <th>F1 </th><th>like_1 </th><th>like_2 </th><th>like_3 </th><th>like_4</th></tr><tr> <td>Catsup </td><td>False </td><td>True </td><td>True </td><td>True</td></tr><tr> <td>Catzup </td><td>False </td><td>False </td><td>False </td><td>True</td></tr><tr> <td>Sup </td><td>True </td><td>True </td><td>True </td><td>False</td></tr><tr> <td>sup </td><td>True </td><td>True </td><td>True </td><td>False</td></tr><tr> <td>superi </td><td>True </td><td>False </td><td>True </td><td>False</td></tr></tbody></table>

          I hope this can help u

          Regards