Weighting Logic in Qlikview Model

[#1]

Hi,

I'm hoping someone can help me with a problem that i'm facing. I'm trying to implement allocation logic for a field in Qlikview based on another field. The data that i have includes customer, product and quantity as listed in the table below. I'm trying to compute the net quantity by product (sum of all quantities for a specific product across all clients) which i've been able to calculate using a load script in the editor and allocate it across the customers with the largest quantities. Below is an example of the output i'm trying to create. In this example, we have multiple customers that are buying and selling a product XXX. The net quantity for product XXX is 8,000 that I need to allocate to the clients with the biggest amounts. In this case, the net quantity should get allocated to clients JKL and MNO (client JKL is buying 5,000 and MNO is buying 3,000 which totals the net quantity of 8,000). The allocated quantity cannot exceed the Quantity against each client.

Customer	Product	Direction	Quantity	Net Quantity	Allocated Quantity
ABC	XXX	Buy	1,000	8,000	0
DEF	XXX	Buy	2,000	8,000	0
GHI	XXX	Sell	-3,000	8,000	0
JKL	XXX	Buy	5,000	8,000	5,000
MNO	XXX	Buy	3,000	8,000	3,000

Any thoughts on how i can implement this either in the editor through a load script or as an expression in a table or a chart?

Thanks,

AC

[sinanozdemir]

Hi,

I am not sure if this is going to work, but you can rank your customers based on the quantity:

Then you can choose the top 2 customers with their quantities:

Hope this helps.

[#3]

Thank you for getting back to me. The problem is, sometimes there are more than 2 customers that make up the net quantity. So the model needs to be smart enough to recognize that.

[aarkay29]

PFA

I might have missed some scenarios where it may show incorrect numbers. But considering those the script have to be adjusted.

Thanks,

Aar

[sinanozdemir]

Hi,

If there are more than two customers that make up the net quantity, then please provide those customers in your sample data.

Thanks

[#6]

Hi Sinan,

The below is a real example from my dataset. I've been able to compute the Net Quantity in the code. The portion i'm struggling with is the Rank and Allocated Quantity. The Rank should display the top few values in the quantity column depending on the sign i.e. if the Net Quantity is negative, it should rank only the negative values in the Quantity field by product/trade id and if the Net Quantity is positive, it should rank all the positive quantities only. The Allocated Quantity should equal the Quantity depending on the direction (if the Net Quantity is negative, allocated quantity will only pick up the negative quantities, etc.) until the sum of the allocated quantity exceeds the Net Quantity. In the below example, after rank 9, the total allocated quantity exceeded the net quantity. So for the customer and trade id in Rank 9, the allocated quantity is just the difference between the sum of the allocated quantity and net. This way, the total allocated quantity equals the net quantity. I hope this isn't too confusing. Any thoughts on how i can implement this will be helpful.

Thanks

Date	Counterparty Name	Product	Trade Id	Quantity	Net Quantity	Rank	Allocated Quantity
2017-07-17	ABC	XXX	N318829E	-1279138	-7411171	2	-1279138
2017-07-17	DEF	XXX	N318828E	-1131124	-7411171	3	-1131124
2017-07-17	GHI	XXX	N318827E	-63919	-7411171	12	0
2017-07-17	JKL	XXX	N317444EE	-784000	-7411171	5	-784000
2017-07-17	JKL	XXX	N317672EE	-840000	-7411171	4	-840000
2017-07-17	JKL	XXX	N317864EE	-446419	-7411171	6	-446419
2017-07-17	JKL	XXX	N319615EE	-155000	-7411171	9	-118230
2017-07-17	JKL	XXX	N319640EE	-435000	-7411171	7	-435000
2017-07-17	JKL	XXX	N320890EE	-145000	-7411171	10	0
2017-07-17	MNO	XXX	N318833E	-2195304	-7411171	1	-2195304
2017-07-17	PQR	XXX	N318832E	-181956	-7411171	8	-181956
2017-07-17	STU	XXX	N312187E	17094	-7411171	0	0
2017-07-17	STU	XXX	N312319E	16936	-7411171	0	0
2017-07-17	STU	XXX	N313100E	236000	-7411171	0	0
2017-07-17	STU	XXX	N313771E	29128	-7411171	0	0
2017-07-17	STU	XXX	N313772E	29128	-7411171	0	0
2017-07-17	STU	XXX	N314252E	20105	-7411171	0	0
2017-07-17	STU	XXX	N320652E	51857	-7411171	0	0
2017-07-17	VWX	XXX	N318831E	-11580	-7411171	14	0
2017-07-17	XYZ	XXX	N318834E	-116908	-7411171	11	0
2017-07-17	ABCD	XXX	N318830E	-20071	-7411171	13	0
2017-07-17	EFGH	XXX	N317902E	-6000	-7411171	15