topic Re: Expression help in QlikView

Expression help

danielnevitt — Mon, 27 Jan 2014 14:26:20 GMT

Hi,

I have an expression that I wish to calculate on a field.

If TRADE_TYPE = Basis Swap then multiply PAYMENT_QTY*(PRICE1-PRICE2)

If TRADE_TYPE = Swap then multiply PAYMENT_QTY*PRICE1

I have created the following expressions for the above, however I am unsure how to express them as a multiple IF statement:

Sum({<TRADE_TYPE={'Basis Swap'}>}PRICE1-PRICE2)*PAYMENT_QTY

Sum({<TRADE_TYPE={'Swap'}>}PRICE1*PAYMENT_QTY

Any help would be much appreciated.

Thanks,

Daniel

Re: Expression help

alexandros17 — Mon, 27 Jan 2014 14:30:40 GMT

the problem is where you place parenthesis:

Sum({<TRADE_TYPE={'Basis Swap'}>} (PRICE1-PRICE2)*PAYMENT_QTY )

Sum({<TRADE_TYPE={'Swap'}>}PRICE1*PAYMENT_QTY )

should have to work (it depends on data)

otherwise try this

Sum({<TRADE_TYPE={'Basis Swap'}>} (PRICE1-PRICE2))*sum(PAYMENT_QTY )

Sum({<TRADE_TYPE={'Swap'}>}PRICE1)*sum(PAYMENT_QTY )

Re: Expression help

jerem1234 — Mon, 27 Jan 2014 14:33:53 GMT

Maybe try this:

if(TRADE_TYPE = 'Basis Swap', Sum({<TRADE_TYPE={'Basis Swap'}>}(PRICE1-PRICE2)*PAYMENT_QTY),

Sum({<TRADE_TYPE={'Swap'}>}PRICE1*PAYMENT_QTY))

Hope this helps!

Re: Expression help

danielnevitt — Mon, 27 Jan 2014 14:35:01 GMT

Hi Alessandro,

Thank you for your reply.

I am still receiving an error message when I set the expression in your reply. Do I need to insert anything after the first expression?

I'm not sure if I was clear in my original message. I require this calculation on the same expression. Basically IF Basis Swap calculate, IF not calculate using the Swap expression.

Thanks,

Daniel

Re: Expression help

Mon, 27 Jan 2014 14:35:36 GMT

Hi Daniel, In the above case you need use the if statement like below:

## If you have only 2 values Basis Swap , Swap in the TRADE_TYPE fields ##

SUM( IF(TRADE_TYPE='Bais Swap' , PAYMENT_QTY*(PRICE1-PRICE2) , PAYMENT_QTY*PRICE1 ))

## If you have more than these 2 values Basis Swap , Swap in the TRADE_TYPE fields ##

SUM( IF(TRADE_TYPE='Bais Swap' , PAYMENT_QTY*(PRICE1-PRICE2) , IF(TRADE_TYPE='Bais Swap' , PAYMENT_QTY*PRICE1 ,0 ) ))

I am inserting 0 in the TRADE_TYPE other that Basis Swap & Swap.

Re: Expression help

alexandros17 — Mon, 27 Jan 2014 15:00:19 GMT

The expression must be placed EACH in one different expression ...

Re: Expression help

danielnevitt — Mon, 27 Jan 2014 17:22:34 GMT

Thank you for the replies.

The last question I have. Is it possible to include in the expression that the COST_REVENUE_IND field must equal Cost for both parts of the expression?

Thanks,

Daniel

Re: Expression help

Mon, 27 Jan 2014 17:36:43 GMT

Hi Daniel, You can add the new field with AND like below:

SUM( IF(TRADE_TYPE='Bais Swap' AND COST_REVENUE_IND='Cost', PAYMENT_QTY*(PRICE1-PRICE2) , IF(TRADE_TYPE='Bais Swap' AND COST_REVENUE_IND='Cost' ,PAYMENT_QTY*PRICE1 ,0 ) ))

Re: Expression help

danielnevitt — Mon, 27 Jan 2014 17:43:48 GMT

That works perfectly. Thank you very much for your help.

Re: Expression help

Mon, 27 Jan 2014 20:23:14 GMT

Your Welcome Daniel.

Re: Expression help

danielnevitt — Thu, 30 Jan 2014 09:48:41 GMT

Hi Dathu.qv,

I have another quick question with regards to the above expression. How do I add where CO_CD is not equal to ICEClearingGS, ImarexClearing and STShippingTranPteLtd?

I thought I needed to add the following, but it doesn't work:

SUM( IF(TRADE_TYPE='Swap' AND COST_REVENUE_IND='Cost (Payable)' AND CO_CD={'*'}-{'ICEClearingGS','ImarexClearingGS','STShippingTranPteLtd'} , PAYMENT_QTY*(-PRICE1) , IF(TRADE_TYPE='Swap' AND COST_REVENUE_IND='Revenue (Receivable)' ,PAYMENT_QTY*PRICE2 ,0 ) ))

Thanks for your help,

Regards,

Daniel