Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for
Did you mean:
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.

Labels (1)
• ### General Question

1 Solution

Accepted Solutions
Specialist III

Hi,

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

``````data:
Change,
InventoryNumber,
InventoryNumber&Periode as key;
Periode,Change, InventoryNumber
202205,05,1E5KS
202207,06,1E5KS
202209,07,1E5KS
202301,01,2G8LM
202304,02,2G8LM];

Concatenate(data)

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

FinaldataTemp:
Periode,
if( (IsNull(Change) or Len(Trim(Change))=0) and previous(InventoryNumber) = InventoryNumber ,previous(Change),Change  ) as Change,
InventoryNumber;
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

Specialist III

Hi,

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

``````data:
Change,
InventoryNumber,
InventoryNumber&Periode as key;
Periode,Change, InventoryNumber
202205,05,1E5KS
202207,06,1E5KS
202209,07,1E5KS
202301,01,2G8LM
202304,02,2G8LM];

Concatenate(data)

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

FinaldataTemp:
Periode,
if( (IsNull(Change) or Len(Trim(Change))=0) and previous(InventoryNumber) = InventoryNumber ,previous(Change),Change  ) as Change,
InventoryNumber;
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