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

Sazabi — Tue, 26 Oct 2021 18:53:32 GMT

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!!!

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

anthonyj — Thu, 28 Oct 2021 05:04:05 GMT

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;

I hope this helps.

Thanks

Anthony

topic Re: Filling in rows between start and end with conditions in Load Script in App Development

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

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