Qlikview Multiple IF statement

[JM_Qlik]

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!!

[edwin]

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

[edwin]

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

[edwin]

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

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