Fetch data by number of transaction

[bryan_21]

Hi, how to fetch data based on the quantity and should match versus number of transaction. Sample data below.

ItemID	Quantity	Quantity Transacted	Date of Transaction
3300658	32	30	6/26/2023
3300658	32	30	8/21/2023
3300658	32	30	9/11/2023
3300658	32	30	10/2/2023
3300658	32	30	12/11/2023
3300658	32	30	2/19/2024
3300658	32	30	3/18/2024
3300658	32	1	4/5/2024
3300658	32	30	5/6/2024

Current quantity is 32, it should fetch the last 3 rows only as it is the last transaction that matches to 32.

[30] [5/6/2024] = 30

[1] [4/5/2024] = 1 + 30 = 31

[30] [3/18/2024] = 1 + 31 = 32

Anyone knows how to do it in load script?

[F_B]

Hi @bryan_21 ,

load ItemID, Quantity, Quantity Transacted, Date of Transaction in a table named Data, then try this:

// Sort data by Date of Transaction in descending order
SortedData:
LOAD
*,
RowNo() as RowID
RESIDENT Data
ORDER BY [Date of Transaction] DESC;

// Accumulate Quantity Transacted
AccumulatedData:
LOAD
*,
RangeSum(Peek('AccumulatedQuantity', RowNo()-1), [Quantity Transacted]) as AccumulatedQuantity
RESIDENT SortedData
ORDER BY RowID ASC;

// Filter to get only the last transactions that match the given criteria
FinalData:
LOAD
*
RESIDENT AccumulatedData
WHERE AccumulatedQuantity <= Quantity
ORDER BY [Date of Transaction] DESC;

DROP TABLE Data;
DROP TABLE SortedData;
DROP TABLE AccumulatedData;