topic Re: string function exlude exceptions in QlikView

string function exlude exceptions

Wed, 18 Sep 2013 10:45:06 GMT

Hi,

Please can some one help.

I am struggling to think of the string function that will allow the following.

I have a list of Customers e.g.

SPMC12345

SPDD12345

SPF12345

SPBB12345

600001

600002

PP4567

Basically all I what to do is take the first 4 letters but exclude the 600001 & 600002 and PP4567 so I end up with w list such as:

SPMC

SPDD

SPF

SPBB

The expression I have used at the moment is:

left([CO Customer No], index([CO Customer No],'0') -1)

This gives me the following:

SPMC

SPDD

SPF

SPBB

But I want to exclude the 6 and PP?

I cannot use a where statement as I do not wish to exclude them altogether just from this calculated field.

Any help welcomed.

Thanks,

Nikki

Gysbert_Wassenaar — Wed, 18 Sep 2013 10:54:22 GMT

if(purgechar(left([CO Customer No],4),'0123456789')<>'PP',purgechar(left([CO Customer No],4),'0123456789'))

Wed, 18 Sep 2013 10:55:17 GMT

hi,

try the follwoing script

if(wildmatch(Fieldname,'600001','600002','PP4567')=0, purgechar ( Fieldname,'0123456789' )) as NEW_FIELD_NAME

CELAMBARASAN — Wed, 18 Sep 2013 11:28:24 GMT

If(not Match(left([CO Customer No], index([CO Customer No],'0') -1) ,'6', 'PP'), left([CO Customer No], index([CO Customer No],'0') -1) )

if(not wildmatch([CO Customer No], '6*', 'PP*'), left([CO Customer No], index([CO Customer No],'0') -1) )

this might helps