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

[contributor_H]

Does anyone have a solution for this?

[Aditya_Chitale]

I have a few questions:

1. in your example, you are tracking Qty * Price on basis of Order ID grouping. So, for 11202, it should be (4*3) + (3*7) + (4*5). But you are considering Qty as 2 for 2nd entry of order 11202. 

2. In your other table, Order ID 11202 has total Qty as 6.

3. Also you are not at all considering the (4*5)  part (4th row in your sample data). Is there any logic behind not to consider that ?

4. the Remaining Qty also shows as 4 for 4th row where Order ID is 11202.

Regards,

Aditya

[contributor_H]

Answers:

1. in your example, you are tracking Qty * Price on basis of Order ID grouping. So, for 11202, it should be (4*3) + (3*7) + (4*5). But you are considering Qty as 2 for 2nd entry of order 11202. 

A1: (4*3) + (3*7) + (4*5) = this is total purchased price (which I bought)

2. In your other table, Order ID 11202 has a total Qty of 6.

A2: this is Sold item. Imagine I sold 6 Qty but these quantities, how were drown from stock?

- first 4 qty were drowned from first order with price 3 (4*3), that item was finished then 2 more items were drowned from second order with price as 7 (2 * 7)

which means now, in stock I still have one more remaining from  12/06/2023 and items from 12/10/2023 (4th row) were untouched because I only sold 6

3. Also you are not at all considering the (4*5)  part (4th row in your sample data). Is there any logic behind not to consider that ? 

A3: because I only sold 6 qty (so I took 4 that I purchased first (1st row) then took 2 from the second purchase (2nd row) and I still have 1 left from second order and 4 items from third (4th row in sample)) are all remaining in the stock

[contributor_H]

Request is clearly explained in below picture:

[Aasir]

First sort the "Stock" table by the "DatePurchased" column in ascending order.
Stock:
LOAD
ID,
Qty,
DatePurchased,
Price
FROM ...
ORDER BY ID, DatePurchased ASC;

Use the ApplyMap function to calculate the remaining quantity based on the order of purchase.

Stock:
LOAD
ID,
Qty,
DatePurchased,
Price,
ApplyMap('RemainingQtyMap', ID & '|' & DatePurchased, 0) as RemainingQty
RESIDENT Stock;

// Map for Remaining Quantity
RemainingQtyMap:
MAPPING LOAD
ID & '|' & DatePurchased,
Sum(Qty) as RemainingQty
RESIDENT Stock
GROUP BY ID, DatePurchased;

In your "Sold" table, load the data and use the ApplyMap function to fetch the remaining quantity and calculate the sold price.

Sold:
LOAD
ID,
Qty,
ApplyMap('RemainingQtyMap', ID & '|' & DatePurchased, 0) as RemainingQty,
ApplyMap('RemainingQtyMap', ID & '|' & DatePurchased, 0) * Price as SoldPrice
RESIDENT Sold;

Now, the "Sold" table should have the ID, Qty, RemainingQty, and SoldPrice.

[contributor_H]

Thanks for the reply!

but how are you applying the map to the sold table with ID and DatePurchased? Because the sold table only has ID and Qty ?

[panosalexand]

Hi Hadiana,

I would recommend you to keep both in one table and add one more column and tag them if it's on stock flag them as H and if it is on sold then flag them as S. 

With this way and within set analysis method you can get the remaining quantity. 

Keep in mind that you will need to to add a master calendar with the purchase date. In this way if you had on 1/1/2023 10 items and made a purchase with 5 items on 5/1/2023 very easy you will be aware that on 5/1/2023 the remaining quantity will be 5 items but also you will know between 1/1 - 4/1 the items were 10. If you don't have a master calendar and select a date which you don't have records then the results you will get back is zero.

You have to be careful how to build this process.

Happy to help you further..

[Aasir]

You won't have the DatePurchased information to directly apply the map. In that case, you'll need to associate the DatePurchased information with the "Sold" data. One way to do this is to join the "Sold" table with the "Stock" table to get the DatePurchased information.
Something like this
SoldWithDate:
LOAD
S.ID as SoldID,
S.Qty as SoldQty,
Stock.ID,
Stock.Qty,
Stock.DatePurchased,
Stock.Price
RESIDENT Sold as S
LEFT JOIN Stock
ON S.ID = Stock.ID
ORDER BY Stock.ID, Stock.DatePurchased ASC;


And now you can use

Sold:
LOAD
ID,
SoldID,
SoldQty,
ApplyMap('RemainingQtyMap', ID & '|' & DatePurchased, 0) as RemainingQty,
ApplyMap('RemainingQtyMap', ID & '|' & DatePurchased, 0) * Price as SoldPrice
RESIDENT SoldWithDate;

// Map for Remaining Quantity
RemainingQtyMap:
MAPPING LOAD
ID & '|' & DatePurchased,
Sum(Qty) as RemainingQty
RESIDENT Stock
GROUP BY ID, DatePurchased;

[contributor_H]

Hi Aasir,

Copied your solution but didn't work, I got the same result, please refer to img attached. I want to get something like the red one.