topic Re: Qlikview Multiple IF statement in QlikView

Qlikview Multiple IF statement

JM_Qlik — Sat, 17 Apr 2021 01:01:41 GMT

Hello,

Hoping someone can help me determine whether it is possible to implement the following logic in Qlik, or whether I need to change my dataset first before i load to Qlik. The datasource is a csv file.

In the Straight Table I have the following (see screenshot):

Dimensions: date, Strategy, Contract (only one visible in screenshot but there are many), Symbol, Buy/Sell (please ignore this dimension), Commission
Expressions: Quantity, Gross Notional, P&L (commission * Gross Notional)

I need to somehow implement the following logic:

IF MATCH "date" AND MATCH "Strategy" AND MATCH "Contract" but "Symbol" does NOT MATCH
Then "Commission" is divided by 2.

So for Person C and Person D, the commission figure would move from 0.04% to 0.02% on both lines.

For Person A there is added complication. If Quantity is not the same on both lines, then the commission rate should only be 0.025% for the lower quantity (1258 in this case). The difference of 74 (1258-1332) should be at 0.05%.

Not sure if this is achievable in Qlik but would really appreciate any guidance on how this could be achieved! Thanks!!

Re: Qlikview Multiple IF statement

edwin — Wed, 21 Apr 2021 20:24:37 GMT

did you mean the Symbol should match but the BuySel_D shouldnt?

Re: Qlikview Multiple IF statement

edwin — Wed, 21 Apr 2021 21:04:25 GMT

Assuming sell is always negative and that you can aggregate in your script
and excluding commission and P&L as these are calculated, this the the starting numbers:

you will need to get the minimum of the quantity when the totals are different, add rows for both buy and sell using the minimum magnitude of quantity then add new rows that hold the difference:

data: load * inline [ Date, Strategy, Contract, Symbol, BuySell, Commision, Quantity 11/3/2021, Person A, KD, IX, Sell, .0005, -31 9/3/2021, Person A, KD, IX, Sell, .0005, -300 9/3/2021, Person A, KD, IX, Sell, .0005, -1000 9/3/2021, Person A, KD, IX, Sell, .0005, -32 9/3/2021, Person A, KD, IX, Buy, .0005, 1000 9/3/2021, Person A, KD, IX, Buy, .0005, 200 9/3/2021, Person A, KD, IX, Buy, .0005, 58 10/3/2021,Person A, KD, IX, Buy, .0005, 200 10/3/2021,Person A, KD, IX, Sell, .0005, -200 ]; data2: NoConcatenate LOAD Date, Strategy, Contract, Symbol, Commision, sum(if(BuySell='Buy', (Quantity))) as BuyQuantity, sum(if(BuySell='Sell', (Quantity))) as SellQuantity Resident data group by Date, Strategy, Contract, Symbol, Commision; drop table data; data3: load Date, Strategy, Contract, Symbol, Commision, 'Buy'as BuySell_D, if(fabs(SellQuantity)=fabs(BuyQuantity), BuyQuantity, rangemin(fabs(SellQuantity),fabs(BuyQuantity)) ) as Quantity resident data2 where fabs(SellQuantity)=fabs(BuyQuantity) or rangemin(fabs(SellQuantity),fabs(BuyQuantity))>0 ; concatenate(data3) load Date, Strategy, Contract, Symbol, Commision, 'Sell' as BuySell_D, if(fabs(SellQuantity)=fabs(BuyQuantity), SellQuantity, -rangemin(fabs(SellQuantity),fabs(BuyQuantity)) ) as Quantity resident data2 where fabs(SellQuantity)=fabs(BuyQuantity) or rangemin(fabs(SellQuantity),fabs(BuyQuantity))>0 ; concatenate(data3) load Date, Strategy, Contract, Symbol, Commision, if(fabs(SellQuantity)>fabs(BuyQuantity), 'Sell','Buy') as BuySell_D, SellQuantity + BuyQuantity as Quantity resident data2 where fabs(SellQuantity)<>fabs(BuyQuantity) or rangemin(fabs(SellQuantity),fabs(BuyQuantity))=0 ; drop table data2;

hope that helps

Re: Qlikview Multiple IF statement

edwin — Wed, 21 Apr 2021 21:07:46 GMT

you can also re-compute Commission, in the 1st 2 concatenate statement, use

if(fabs(sellQuantity)=fabs(buyQuantity), Commission/2, Commission) as Commission