Hello Qlik Community,
I'm working on calculating the maximum gap in months where there is no "Required Quantity Month" for part numbers. Null or zero values indicate no requirement for the month. The script should output "Start Gap" and "End Gap" as dates and "MaxGapMonths" for each part number. I'm encountering issues with null or blank values and incorrect gap calculations. Below is the script section where I believe the issue lies.
// Load the initial data with necessary transformations
ColdStarts1:
LOAD
[Part Number],
// ... other fields ...
FROM [lib://YourDataSource/COLD_ABCD_TBL.qvd](qvd);
// Load monthly data, replacing nulls or blanks with zeros
MonthlyData:
LOAD
[Part Number],
Date(Date#([Month], 'MM-YYYY'), 'MM-YYYY') as MonthDate,
If(Len(Trim([Required Quantity Month (ZMRPORD)])) > 0, Num([Required Quantity Month (ZMRPORD)]), 0) as RequiredQty // Exclude nulls/blanks
FROM [lib://DataSource/ZXY.qvd](qvd);
// Sorting data by Part Number and MonthDate
SortedMonthlyData:
LOAD
[Part Number],
MonthDate,
RequiredQty,
If(RequiredQty = 0, 1, 0) as NoQtyFlag // Flag months with zero quantity after excluding nulls/blanks
Resident MonthlyData
Order By [Part Number], MonthDate Asc;
// Calculate the gaps in a new table
GapTable:
LOAD
[Part Number],
MonthDate,
RequiredQty,
NoQtyFlag,
If(NoQtyFlag = 1 and Peek('NoQtyFlag') = 1 and [Part Number] = Peek([Part Number]),
(Year(MonthDate) - Year(Peek('MonthDate'))) * 12 + Month(MonthDate) - Month(Peek('MonthDate')),
0) as GapMonths // Calculate the gap only if the previous month had no quantity for the same part number
Resident SortedMonthlyData
Where NoQtyFlag = 1;
// Identifying the maximum gap for each part number
MaxGapTable:
LOAD
[Part Number],
Max(GapMonths) as MaxGapMonths
RESIDENT GapTable
GROUP BY [Part Number];
// Joining the maximum gap information back to the GapTable
Left Join (GapTable)
LOAD
[Part Number],
MaxGapMonths
RESIDENT MaxGapTable;
DROP TABLE MaxGapTable;
// Finding the actual start and end dates for the maximum gap
Left Join (ColdStarts1)
LOAD
[Part Number],
Min(If(GapMonths = MaxGapMonths and GapMonths > 0, MonthDate)) as [Start Gap],
Max(If(GapMonths = MaxGapMonths and GapMonths > 0, MonthDate)) as [End Gap]
RESIDENT GapTable
GROUP BY [Part Number];
DROP TABLE GapTable;
DROP TABLE SortedMonthlyData;
DROP TABLE MonthlyData;
