Pattern in logic test

[arsenal1983]

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]

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

- Marcus

[veeranj]

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

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

-Veera

[MarcoWedel]

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]

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]

Hi Lukasz,

                      What about:

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

Good Luck

Andrew