topic Re: Match Expression in QlikView

Match Expression

Fri, 17 Jul 2015 10:05:01 GMT

Hi Experts,

How to write the below expression, I have to show this(Error and Warning) in List box as a filter. I have tried with Wildmatch but its case insensitive so I am not able to get it.

I have Prodcut column Ex: Its just an example, In real I have millions of records

Product
Abc
ABC
cBc
CBc

KZX

kYS
]

I need to expression like below:

if PRODUCTS IN ('%b%', '%k%') as Error

if PRODUCTS IN ('%B%','%K%') as WARNING

Then my List box should show like

Exception(Filter name)

Error

Warning

So Error should be equal to - Abc and kYS

Warning should be equal to - ABC,cBc,CBc,KZX

Re: Match Expression

datanibbler — Fri, 17 Jul 2015 10:14:15 GMT

Hi Raj,

for that you just need the function WildMatch() - that allows you to match one value (or the value of one field) against several values - and act accordingly. You can wrap it like PICK(MATCH()) to pick an action or output a specific value, depending on which value the field in one specific row matches.

Wildmatch() allows you to use wildcards - the wildcard for any number of letters is the asterisk *

So, your expression will be sth like

>> PICK(Wildmatch(Product, *b*, *k*, *B*, *K*), 'Error', 'Error', 'Warning', 'Warning') <<

If you do that in the script in a LOAD, you can give this any fieldname and then you can show a listbox of that field.

HTH

Re: Match Expression

er_mohit — Fri, 17 Jul 2015 10:17:44 GMT

Write in expression

Pick(Wildmatch(Product,'*b*','*k*','*B*','*K*'),'Error','Error','WARNING','WARNING')

You can use also the same in backend script like

Pick(Wildmatch(Product,'*b*','*k*','*B*','*K*'),'Error','Error','WARNING','WARNING') as flag

hope it helps

Re: Match Expression

Fri, 17 Jul 2015 10:21:31 GMT

but it showing only Error as it comparing both small letter or upper letter as one

Re: Match Expression

Fri, 17 Jul 2015 10:21:40 GMT

but it showing only Error as it comparing both small letter or upper letter as one

Re: Match Expression

MK_QSL — Fri, 17 Jul 2015 10:34:13 GMT

IF(Index(Product,'b')>0 or Index(Product,'k')>0, 'error',

IF(Index(Product,'B')>0 or Index(Product,'K')>0, 'Warning')) as Flag

Re: Match Expression

Fri, 17 Jul 2015 10:37:59 GMT

Perfect

Re: Match Expression

sasiparupudi1 — Fri, 17 Jul 2015 10:48:21 GMT

You can not use wildmatch in your scenario as it does a case insensitive search. Please try the following

Table1:

LOAD *Inline

[

Product

Abc

ABC

cBc

CBc

KZX

kYS

];

NoConcatenate

LOAD Product,

//if (WildMatch(Product,'?b?', '?k?'),1,0) as Error,

//if (WildMatch(Product,'?B?', '?K?'),1,0) as Warning

if (SubStringCount(Product,'b')>0 or SubStringCount(Product,'k') ,1,0) as Error,

if (SubStringCount(Product,'B')>0 or SubStringCount(Product,'K') ,1,0) as Warning

Resident Table1;

drop Table Table1;