Filtering and subtract based on multiple columns

[contributor_H]

Dear community,

I was wondering if I can achieve the following:

I have the below source table I want to use simple basic filter logic to get the output result below

Logic: based on Inv_Qty availble and Qunatity I want to achieve the following (for example item 11202, I have three quantity 4, 3, 4 but Inv_qty is 5 so I want to show 4, 1 only as shown in output table sorted by old to new)  rows are subtracted and shown from old to new.

source table:

ItemID	Inv_Qty	Quantity	Date	Price
5050	7	4	11/10/2023	60
5050	7	8	12/12/2023	70
11202	5	4	01/01/2023	3
11202	5	3	12/06/2023	7
11202	5	4	12/10/2023	5

desired output:

ItemID	Inv_Qty	Quantity	Date	Price
5050	7	4	11/10/2023	60
5050	7	3	12/12/2023	70
11202	5	4	01/01/2023	3
11202	5	1	12/06/2023	7

[contributor_H]

this could be a simple expression, but I can not get my head around it, appreciate if someone can help 

[Saravanan_Desingh]

Try something like this:

tab1:
LOAD *, If(ItemID=Peek(ItemID), Peek(K1)-Quantity,Inv_Qty-Quantity) As K1,
	If(ItemID=Peek(ItemID), If(Peek(K1) > Quantity, Quantity, If(Peek(K1) < 0, 0, Peek(K1))), Quantity) As New_Quantity
;
LOAD RecNo() As RowID, * INLINE [
    ItemID, Inv_Qty, Quantity, Date, Price
    5050, 7, 4, 11/10/2023, 60
    5050, 7, 8, 12/12/2023, 70
    11202, 5, 4, 01/01/2023, 3
    11202, 5, 3, 12/06/2023, 7
    11202, 5, 4, 12/10/2023, 5
    11203, 10, 4, 01/01/2023, 3
    11203, 10, 2, 12/06/2023, 7
    11203, 10, 4, 12/10/2023, 5
];

Drop Field K1;

[contributor_H]

thanks Saravanan_Desingh ,

this is somehow working but it's not with the date, I want the oldest one to be processed first.

for example if you change (12/12/2023 of ID 5050 to 12/12/2022) now this is the oldest for the ID 5050 and new quantity should be 7 

[Saravanan_Desingh · Accepted solution]

Try like this,

tab1:
LOAD * INLINE [
    ItemID, Inv_Qty, Quantity, Date, Price
    5050, 7, 4, 11/10/2023, 60
    5050, 7, 8, 12/12/2022, 70
    11202, 5, 4, 01/01/2023, 3
    11202, 5, 3, 12/06/2023, 7
    11202, 5, 4, 12/10/2023, 5
    11203, 10, 4, 01/01/2023, 3
    11203, 10, 2, 12/06/2023, 7
    11203, 10, 4, 12/10/2023, 5
];

tab2:
NoConcatenate
LOAD RecNo() As RowID, *, If(ItemID=Peek(ItemID), Peek(K1)-Quantity,Inv_Qty-Quantity) As K1,
	If(ItemID=Peek(ItemID), If(Peek(K1) > Quantity, Quantity, If(Peek(K1) < 0, 0, Peek(K1))), If(Quantity < Inv_Qty, Quantity, Inv_Qty)) As New_Quantity
Resident tab1
Order By ItemID, Date
;

Drop Field K1;
Drop Table tab1;