Filling gaps in a table through manipulation

[luke_brady]

Hello all,

I have something similar to the following table:

Periode           Change            InventoryNumber

202205                05                              1E5KS

202207                06                              1E5KS

202209                07                              1E5KS

202301                01                              2G8LM

202304               02                               2G8LM

I've been thinking about how to go about it with the Qlik View loading script to create a table that would look like this:

Periode           Change            InventoryNumber

202205                05                              1E5KS

202206                05                              1E5KS    

202207                06                              1E5KS

202208                06                              1E5KS

202209                07                              1E5KS

202301                01                              2G8LM

202302                01                              2G8LM

202303                01                              2G8LM

202304                02                              2G8LM

Does anyone have an idea how to do this elegantly? until now I've been thinking about using PEEK() and AUTOGENERATE but I'm struggling.

Thank you in advance.

[ajaykakkar93 · Accepted solution]

Hi,

The provided solution is elaborate, but it can be condensed based on the data model.

data:
load date(date#(Periode,'YYYYMM'),'YYYYMM') as Periode,
	Change,
	InventoryNumber,
    InventoryNumber&Periode as key;
load * Inline [
Periode,Change, InventoryNumber
202205,05,1E5KS
202207,06,1E5KS
202209,07,1E5KS
202301,01,2G8LM
202304,02,2G8LM];


Concatenate(data)

load InventoryNumber, date(MonthName(Missing_Date),'YYYYMM') as Periode
where not(Exists(key,InventoryNumber&date(MonthName(Missing_Date),'YYYYMM')));
load 
 	InventoryNumber,
 	 Date([Starttime] + IterNo() - 1) as Missing_Date
While ([Starttime] + IterNo() - 1) <= [EndTime];
load 
	InventoryNumber,
    date(Max(Periode)) as EndTime,
    date(Min(Periode)) as Starttime
Resident data
Group by InventoryNumber;

FinaldataTemp:
load Distinct * where not(IsNull(Change));
load Distinct
	Periode,
 	if( (IsNull(Change) or Len(Trim(Change))=0) and previous(InventoryNumber) = InventoryNumber ,previous(Change),Change  ) as Change,
	InventoryNumber;
load Distinct
	InventoryNumber,
    Change,
	Periode,
    15425155 as jjj
Resident data
Order by InventoryNumber , Periode ;

drop Tables data;

exit Script;