topic String Function in QlikView

String Function

Tue, 22 Nov 2011 12:15:30 GMT

Hi all,

I want to calculate the fields based on the following condition.

If the material code ends with 'S' then the quantity should be multiplied by 2 else the quantity remains the same.

I tried the below codition but it results with some syntax error.

if([Material code] like '%s', ([Quantity]*2),[Quantity]) as [Billed Quantity]

Please provide me some solution to make it work

Thanks,

Gaayathri

String Function

swuehl — Tue, 22 Nov 2011 12:18:09 GMT

Try a string function like right()

if(right([Material code],1)='s', ([Quantity]*2),[Quantity]) as [Billed Quantity]

String Function

Tue, 22 Nov 2011 12:28:00 GMT

Hi Swuehl,

Thanks for your spontaneous reply!!!

I have tried your solution, but again i m getting the syntax error..

Can u please suggest some other idea for my condition?

Thanks,

Gaayathri

String Function

swuehl — Tue, 22 Nov 2011 12:33:43 GMT

Hm, I believe the above approach should work out.

[Material code] and [Quantity] have to be fields from your input table, not renamed fields or originated in another table.

Could you post your complete LOAD script of this table load?

String Function

Tue, 22 Nov 2011 12:43:23 GMT

Acctually These are from SAP Tables...

I have copied the fields until the requeired Field.

[VBRP]:

Load

// Key fields:

[VBELN] as [%BillingDocument_Key],

[VBELN] & '/' & [POSNR] as [%BillingDocumentItem_Key],

// Additional fields:

([NETWR] - [WAVWR]) as [Billing Sales Margin Amount],

1 as [Billing Item Counter],

// Renamed fields:

[MATNR] as [Billing Material_MATNR],

[FKIMG] as [Billing Sales Quantity_FKIMG],

[NETWR] as [Billing Sales Amount_NETWR],

[MEINS] as [Billing Base Unit_MEINS],

if(right([MATNR],1)='s', ([FKIMG]*2),[FKIMG]) as [Billed Quantity_FKIMG];

SQL SELECT VBELV POSNV VBELN POSNN

FROM VBFA WHERE (VBTYP_N ='M' OR VBTYP_N ='O') AND (VBTYP_V ='J' OR VBTYP_V ='T');

String Function

swuehl — Tue, 22 Nov 2011 12:49:42 GMT

Maybe I just don't get something here, but some of the fields you use in your load part are not part of the SQL SELECT, right? I believe this won't work then, you need to get those field from the DB using SQL SELECT first.