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;
