Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

arsenal83
Not applicable

Pattern in logic test

Hello,

I have a set of data with invoice number (INVOICE_NO) and value (INVOICE_VALUE)

I need to transform INVOICE_VALUE when the first 3 letters of INVOICE_NO match the pattern (nn/)

example if INVOICE_NO begins on: 16/ 17/ 99/ 14/ I would like to multiply INVOICE_VALUE by 3.

any suggestions?

Tags (1)
5 Replies
marcus_sommer
Not applicable

Re: Pattern in logic test

Maybe something like this: if(mid(INVOICE_NO, 3, 1) = '/' and isnum(mid(INVOICE_NO, 1, 2)), 3, 'anotherValue').

- Marcus

anjeepvr
Not applicable

Re: Pattern in logic test

load * ,if(string='/',value*3,value) as newvalue;

load *,mid(invoice,3,1) as string Resident tab1;

-Veera

MarcoWedel
Not applicable

Re: Pattern in logic test

maybe helpful:

LOAD If(INVOICE_VALUE like '??/*' and IsNum(Left(INVOICE_VALUE,2)), INVOICE_VALUE*3, INVOICE_VALUE) as INVOICE_VALUE

FROM YourSource;

regards

Marco

swuehl
Not applicable

Re: Pattern in logic test

If you need to test the start of your string for certain values, like '16/', '17/', '99/',  or  '14/',  you can also use the Match() function:


LOAD

     INVOICE_NO,

     If( Match( Left( INVOICE_NO,3),  '16/', '17/', '99/', '14/'), 3, 1) * INVOICE_VALUE  As INVOICE_VALUE,

     ...

FROM ....;

effinty2112
Not applicable

Re: Pattern in logic test

Hi Lukasz,

                      What about:

If(IsNum(Left(INVOICE_NO,2)) AND Mid(INVOICE_NO,3,1( = '/'),3*INVOICE_VALUE,INVOICE_VALUE)

Good Luck

Andrew