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;
