topic Like Operator in QlikView

Like Operator

Anonymous — Fri, 11 Jun 2010 22:21:54 GMT

Could anybody please tell me how do i use like oprator, i have a field for which i need to filter based on first 3 letter

usually we do something like (product like 'msm%' ) as MSM or (product like 'qsc%') as 'QCS'

and using these MSM,QSC i need to have a group ,

Could anybody please assit me in doing this?

Thansk in Advance

Like Operator

johnw — Fri, 11 Jun 2010 22:38:54 GMT

You can use the wildmatch() function as a like operator:

wildmatch(product,'msm*')

And build your groups something like this:

pick(wildmatch(product,'msm*','qsc*'),'MSM','QSC') as ProductGroup

However, if you're just trying to group on the first three characters:

left(product,3) as ProductGroup

Like Operator

Anonymous — Fri, 11 Jun 2010 22:53:42 GMT

Thank you for the reply ,

so wild match has to be used in load properties or in the select query , i am new to qlikview ...

sorry if this is a silly question...

Like Operator

johnw — Fri, 11 Jun 2010 23:10:40 GMT

The LOAD portion of the script uses QlikView syntax and functions. The SELECT portion of the script uses SQL syntax and functions, and is passed directly to your ODBC driver to execute on your DBMS, and is NOT processed or interpreted by QlikView so far as I know.

LOAD
<QlikView syntax and functions>
;
SQL
<SQL syntax and functions>
;

So for example:

LOAD
OIID as "Order Item"
,OICUST as "Customer"
,OIPROD as "Product"
,if(wildmatch(OIPROD,'msm*'),'MSM','Other') as "Product Group"
;
SQL
SELECT
OIID
,OICUST
,OIPROD
FROM BLAH.ORDERITEM
WHERE OITYP LIKE 'ORDER*'
;

Like wouldn't work in the load, and wildmatch wouldn't work in the select. Each uses their own syntax.