Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
this could be a simple expression, but I can not get my head around it, appreciate if someone can help
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;
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
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;