Subtracting from Row by Order of their date

[contributor_H]

Hello Everyone,

I want to achieve below in Qlik.

Assume I have a table named "Stock" that includes the items I have purchased with their quantity and individual price

Stock:

ID	Qty	DatePurchased	Price
11202	4	01/01/2023	3
11202	3	12/06/2023	7
5050	4	11/10/2023	60
11202	4	12/10/2023	5
5050	8	12/12/2023	70

Let us say I sold 6 items with ID "11202" and that is stored in another table 

Sold:

ID	Qty
11202	6

I want to add a new column that will include the total price but first it will count quantity and price in order 

so in this case 

4 * 3 = 12 (first order in date 01/01/2023) remaining stock 0

2 * 7 = 14 (second order in date 12/06/2023) remaining stock 1

12 + 14 = 26 total price

ID	Qty	Sold Price
11202	6	26

to keep track of qty we can add a new column remaining qty

ID	Qty	DatePurchased	Price	remaining qty
11202	4	01/01/2023	3	0
11202	3	12/06/2023	7	1
5050	4	11/10/2023	60	4
11202	4	12/10/2023	5	4
5050	8	12/12/2023	70	8

[Ahidhar · Accepted solution]

try this getting the result with this

stock:
load * Inline
[
ID, Qty, DatePurchased, Price
11202, 4, 01/01/2023, 3
11202, 3, 12/06/2023, 7
5050, 4, 11/10/2023, 60
11202, 4, 12/10/2023, 5
5050, 8, 12/12/2023, 70
];

sold:
load ID,"Total Qty" as TotalQty;
load * inline
[
ID, Total Qty
11202, 6
5050,4
];
left join(sold)
load * resident stock where exists(ID);drop table stock;

NoConcatenate
sold1:
load *,
if(TotalQty=qty,Price2,
if(TotalQty>previous(qty) and TotalQty<qty,
rangesum(previous(Price2))+(TotalQty-Previous(qty))*Price)) as SoldPrice;
load *,
if(ID=previous(ID),rangesum(peek(Price2),Price1),Price1) as Price2;
load *,
if(ID=previous(ID),rangesum(peek(qty),Qty),Qty) as qty,
Price*Qty as Price1
resident sold order by ID, DatePurchased asc; drop table sold;

sold:
load ID,TotalQty,SoldPrice
resident sold1 where exists(SoldPrice);
drop table sold1;

[contributor_H]

Hi Ahidhar, 

I tried your solution it's working only for one row, 

when I added a new row didn't work.

is there a way that this can be solved with loops, to loop through IDs then decrease them by order, and updating the columns? 

[Ahidhar]

I have made changes to my previous script now it is working for both 5050,11202 ID try that one