Help Needed: 'StartGap' and 'EndGap' Fields Returning Null in Inventory Gap Analysis Script

[Ayden]

Title:
Help Needed: ‘StartGap’ and ‘EndGap’ Fields Returning Null in Inventory Gap Analysis Script

Body:
Dear Qlik Community,

I am currently working on an inventory analysis project in Qlik Sense where I need to identify periods of inactivity, referred to as ‘gaps’, for various part numbers. A gap is defined as a period where the quantity of a part number remains at zero across consecutive months. My goal is to determine the ‘StartGap’ and ‘EndGap’ for these periods, but I’m running into an issue where these fields are populated with null values for all entries.

Here’s what I’m trying to achieve:

• For each part number, identify the last month with a required quantity of zero (this becomes the ‘StartGap’).
• Then identify the subsequent period where the required quantity resumes to more than zero (this becomes the ‘EndGap’).

However, after running my script, the ‘StartGap’ and ‘EndGap’ fields are not showing the expected dates—they are all null. Below is the script that I’ve been using to perform this calculation:

// Load the initial cold parts data
ColdStarts1:
LOAD
[Part Number],
// ... other fields ...
FROM [lib://DummyPath/COLD_PARTS_TBL.qvd](qvd);

// Load monthly data, handling nulls or blanks
MonthlyData:
LOAD
[Part Number],
Date(Date#([Month], 'MM-YYYY'), 'MM-YYYY') as MonthDate,
Alt(Num([Required Quantity Month (ZMRPORD)]), 0) as RequiredQty // Substitute nulls with 0
FROM [lib://DummyPath/MRPORD.qvd](qvd);

// Sorting data by Part Number and MonthDate
SortedMonthlyData:
LOAD
[Part Number],
MonthDate,
RequiredQty,
If(RequiredQty = 0, 1, 0) as NoQtyFlag
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 [Part Number] = Peek('[Part Number]') and Peek('NoQtyFlag') = 0, MonthDate, Null()) as StartGap,
If(NoQtyFlag = 1 and ([Part Number] <> Peek('[Part Number]') or Peek('NoQtyFlag') = 0), MonthDate, Null()) as EndGap
Resident SortedMonthlyData
Where NoQtyFlag = 1;

I’ve confirmed that the data is sorted correctly, and I’ve been careful to ensure the field names match across the script. However, the outcome is not as expected, with null values across the board.

Attached is a screenshot showing the resulting null values in the ‘StartGap’ and ‘EndGap’ columns on my sheet.

 

I would greatly appreciate any insight or suggestions from the community on what might be going wrong with my script and how I might resolve these null values.

Thank you so much for your assistance!

[Ahidhar]

Use previous instead of peek 

[Aasir]

Peek can cause this, replaced the Peek() function with Previous() and Next()

// Load the initial cold parts data
ColdStarts1:
LOAD
[Part Number],
// ... other fields ...
FROM [lib://DummyPath/COLD_PARTS_TBL.qvd](qvd);

// Load monthly data, handling nulls or blanks
MonthlyData:
LOAD
[Part Number],
Date(Date#([Month], 'MM-YYYY'), 'MM-YYYY') as MonthDate,
Alt(Num([Required Quantity Month (ZMRPORD)]), 0) as RequiredQty // Substitute nulls with 0
FROM [lib://DummyPath/MRPORD.qvd](qvd);

// Sorting data by Part Number and MonthDate
SortedMonthlyData:
LOAD
[Part Number],
MonthDate,
RequiredQty,
If(RequiredQty = 0, 1, 0) as NoQtyFlag
Resident MonthlyData
Order By [Part Number], MonthDate Asc;

// Create a flag to identify the StartGap and EndGap
GapTable:
LOAD
[Part Number],
MonthDate,
RequiredQty,
NoQtyFlag,
If(NoQtyFlag = 1 and Previous(NoQtyFlag) = 0, MonthDate, Null()) as StartGap,
If(NoQtyFlag = 1 and Next(NoQtyFlag) = 0, MonthDate, Null()) as EndGap
Resident SortedMonthlyData
Where NoQtyFlag = 1;