Historical Open Order Records

KGosh — Fri, 10 Jan 2025 20:12:28 GMT

Hello,

I need to capture the status of orders when they were open, even if they are now closed, and I would like to create a historical snapshot of the data. When I attempted to add this in the script, I still receive 0 values for weeks since they are now closed. I attempted to create a WeeklySnapshot Date field, but this is not working. Any assistance would be greatly appreciated.

Status66Snapshot:
LOAD
"DO Number" as ReturnsOrderNumber,
"DO Line Status" as ReturnsLineStatus,
"DO Item Number" as ReturnsItemNumber,
// applymap('DMRClassification',trim("DO Item Number") ,'Non-DMR') as ReturnsDMR,
DO_Transaction_Quantity as ReturnsQuantity,
"DO Creation Date" as ReturnsCreationDate,
WeekStart("DO Creation Date") as WeeklySnapshotDate,
"DO Transaction Date" as ReturnsTransactionDate,
"DO To Warehouse" as DOToWarhouse,
applymap('WarehouseClassificationMap',"DO To Warehouse",'NA') as ReturnsToWarhouseLocation,
"Order Entry Age" as ReturnsAgingBucket,
"DMR Parts" as ReturnsDMR,
WeekEnd(Today()) as SnapshotTimestamp

// "DO Creation Date" as SnapshotDate

Resident ReturnsTable
Where "DO Line Status" = 66 ;

DMRKPI:
LOAD
ReturnsOrderNumber,
ReturnsLineStatus,
ReturnsItemNumber,
ReturnsDMR,
SUM(IF( ReturnsToWarhouseLocation='ELP-RTNVRL' or ReturnsToWarhouseLocation='PSC-RTN', ReturnsQuantity)) as Status66,
// SUM(IF(ReturnsDMR = 'DMR' and ReturnsToWarhouseLocation='ELP-RTNVRL', ReturnsQuantity)) as Status66,
// SUM(IF(ReturnsDMR = 'DMR' and ReturnsToWarhouseLocation='ELP-SSL', ReturnsQuantity)) as Status66ToSSL,
SUM(IF(ReturnsToWarhouseLocation='ELP-SSL', ReturnsQuantity)) as Status66ToSSL,
ReturnsQuantity,
WeeklySnapshotDate,
ReturnsCreationDate,
ReturnsTransactionDate,
ReturnsToWarhouseLocation,
sum(if(ReturnsAgingBucket='30 - 60 Days' or ReturnsAgingBucket='60 - 90 Days' or ReturnsAgingBucket='90 Days +',ReturnsQuantity)) as Status66AgingBucket,
SnapshotTimestamp
Resident Status66Snapshot
Group By
ReturnsOrderNumber,
ReturnsLineStatus,
ReturnsItemNumber,
ReturnsDMR,
ReturnsQuantity,
ReturnsCreationDate,
ReturnsTransactionDate,
ReturnsToWarhouseLocation,
WeeklySnapshotDate,
ReturnsAgingBucket,
SnapshotTimestamp;

Re: Historical Open Order Records

Bhushan_Mahajan — Mon, 13 Jan 2025 06:05:33 GMT

@KGosh Try using SCD type 2 or Type 3

topic Historical Open Order Records in App Development

Historical Open Order Records

Re: Historical Open Order Records