topic SQL "Like" in QlikView in QlikView

SQL "Like" in QlikView

shumailh — Wed, 29 Jul 2009 23:51:03 GMT

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

hector — Thu, 30 Jul 2009 00:11:06 GMT

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

SQL "Like" in QlikView

shumailh — Thu, 30 Jul 2009 00:21:36 GMT

Thanks Héctor! that's working

Regards,
Shumail Hussain