Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Elarbe
Contributor III
Contributor III

Filling gaps in a table through manipulation

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.

 

Labels (1)
1 Solution

Accepted Solutions
ajaykakkar93
Specialist III
Specialist III

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;



Please mark the correct replies as Solution. Regards, ARK
Profile| GitHub|YouTube|Extension|Mashup|Qlik API|Qlik NPrinting

View solution in original post

1 Reply
ajaykakkar93
Specialist III
Specialist III

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;



Please mark the correct replies as Solution. Regards, ARK
Profile| GitHub|YouTube|Extension|Mashup|Qlik API|Qlik NPrinting