Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Sazabi
Creator
Creator

Filling in rows between start and end with conditions in Load Script

Hi Qliksters,

I have a table here and would like to generate dates per object + carry over the status between dates

For example: Given this table

Object Date Status
A 1/1/2021 Active
A 1/3/2021 Inactive
B 1/2/2021 Inactive
B 1/6/2021 Active

 

Make a table that looks like:

Object Date Status
A 1/1/2021 Active
A 1/2/2021 Active
A 1/3/2021 Active
A 1/4/2021 Inactive
A 1/5/2021 Inactive
A 1/6/2021 Inactive
B

1/1/2021

NULL
B 1/2/2021 Inactive
B 1/3/2021 Inactive
B 1/4/2021 Inactive
B 1/5/2021 Inactive
B 1/6/2021 Active

 

In the load script, is there a way to do so with some sort of looping mechanism?

 

 

REF:
LOAD * INLINE [Object, Time, Status
A, 1/1/2021, Active
A, 1/3/2021, Inactive
B, 1/2/2021, Inactive
B, 1/6/2021, Active];

Calendar:
LOAD * INLINE [Object, Time
A, 1/1/2021
A, 1/2/2021
A, 1/3/2021
A, 1/4/2021
A, 1/5/2021
A, 1/6/2021
B, 1/1/2021
B, 1/2/2021
B, 1/3/2021
B, 1/4/2021
B, 1/5/2021
B, 1/6/2021];

 

Thanks!!!

 

1 Reply
anthonyj
Creator III
Creator III

Hi @Sazabi ,

This code will fill out the missing dates and forward fill your missing status based on the objects being the same as the previous record.

data:
load * Inline [
Object Date Status
A 1/1/2021 Active
A 1/3/2021 Inactive
B 1/2/2021 Inactive
B 1/6/2021 Active
](delimiter is '\t');

//Load a distinct list of objects so the to the master date table will duplicate with the number of objects
Objects:
Load distinct
Object
Resident data;

//Loop through from the minimum to maximum dates
join(Objects)
Load
date(monthstart(MinDate, IterNo()-1)) as Date
while monthstart(MinDate, IterNo()-1) <= MaxDate;
load
min(Date) as MinDate,
max(Date) as MaxDate
Resident data;

// Join back to the data using the object and date as a key
Join(data)
Load
*
Resident Objects;

NoConcatenate

// Create a new status field based on Status and forward fill missing values where appropriate.
Output:
Load
*,
if( Object <> peek('Object'),
Status,
if(not isnull(Status), Status, peek('NextStatus')) ) as NextStatus
Resident data
order by Object, Date;


drop table Objects, data;

anthonyj_0-1635397404997.png

 

I hope this helps.

Thanks

Anthony