Match Expression

[#1]

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

[datanibbler]

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

[er_mohit]

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

[#4]

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

[#5]

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

[MK_QSL · Accepted solution]

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

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

[#7]

Perfect

[sasiparupudi1]

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;