topic Re: Pattern in logic test in QlikView

Pattern in logic test

arsenal1983 — Thu, 18 Aug 2016 12:56:13 GMT

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?

marcus_sommer — Thu, 18 Aug 2016 13:04:51 GMT

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

- Marcus

veeranj — Thu, 18 Aug 2016 13:11:03 GMT

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

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

-Veera

MarcoWedel — Sat, 20 Aug 2016 01:54:05 GMT

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 — Sat, 20 Aug 2016 11:36:29 GMT

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 — Sat, 20 Aug 2016 11:55:16 GMT

Hi Lukasz,

What about:

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

Good Luck

Andrew