Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Fill the missing dates with the Previous days Value

Hello Experts,

Could you please help me with the problem I am facing?

I would like to fill the missing dates and it values from the previous day's values.

I tried multiple logic provided in the community,unfortunately I didn't get the required output.

This may be because of the data I have.

Here are the challenges.

ID will have multiple rows per day

If there is no data for a particular day,then I want all the rows from previous day's data to be populated and have a new date as missing date and this needs to created until today

Attaching the sample data which will help more

Regards

Jeba

17 Replies
Anonymous
Not applicable
Author

Hi Marco,

Unfortunately this is not working in my case

Anonymous
Not applicable
Author

No this doesn't work.

I already tested multiple options

Anonymous
Not applicable
Author

Hi Marco,

My data has multiple rows per day and I want all the rows to be copied to the next day if it is missing.

Unfortunately this works only for the last row and previous rows were skipped

Any thoughts?

Regards

Jeba

Anonymous
Not applicable
Author

Sure. no issues

RadovanOresky
Partner Ambassador
Partner Ambassador

Hi, this should work as you need. (I wrote it quickly so sorry if there are mistakes but I believe you can get the point )

Sample:

noconcatenate load

ID &''& num(PERIOD) &''& Type as _key_exists,

ID,

PERIOD,

Type,

Limit,

Required

from

[..\Downloads\Sample.xlsx]

(ooxml, embedded labels, table is Sample);

t_missing_records:

noconcatenate load

ID, Type, min(PERIOD) as PERIOD_start

resident

Sample

group by

ID, Type;

join_all_dates:

join (t_missing_records) load

t_min_date + iterno() as PERIOD

while

t_min_date + iterno() <= t_max_date;

noconcatenate load

min( fieldvalue( 'PERIOD', recno() ) ) - 1 as t_min_date,

max( fieldvalue( 'PERIOD', recno() ) ) as t_max_date

autogenerate

fieldvaluecount( 'PERIOD' );

concatenate (Sample) load

ID,

PERIOD,

Type

resident

t_missing_records

where

not exists( _key_exists, ID &''& num(PERIOD) &''& Type )

and num(PERIOD) >= PERIOD_start;

drop table t_missing_records;

Final:

noconcatenate load

ID,

PERIOD,

Type,

if( ID = peek(ID) and Type = peek(Type), peek(Limit), Limit ) as Limit,

if( ID = peek(ID) and Type = peek(Type), peek(Required), Required ) as Required

resident

Sample

order by

ID, Type, PERIOD asc;

drop table Sample;

I didn't review your code but the problem might have been in the last IF statements with the peek().

Hope it will help.

Radovan

Anonymous
Not applicable
Author

Hi Radovan

Thanks for the guidance.

The code works perfectly fine with the data sample.

However this doesn't work when we add the new data.

Please refer the highlighted rows and the output in the right side.

This code create multiple records based on the _key_exists.

The final output should be exactly as of previous day irrespective of the data changes.

Is that possible?

Screenshot.png

RadovanOresky
Partner Ambassador
Partner Ambassador

Ahh, yeah you're right, I missed that.

Well a quick fix would be to change the last resident load like this:

Final:

noconcatenate load

ID,

PERIOD,

Type,

Limit,

Required,

if( isnull(Limit) and ID = peek(ID) and Type = peek(Type), peek(Limit_new), Limit ) as Limit_new,

if( isnull(Required) and ID = peek(ID) and Type = peek(Type), peek(Required_new), Required ) as Required_new

resident

Sample

order by

ID, Type, PERIOD asc;

So the "_new" fields will now replicate the previous number if there is NULL, otherwise they will include the source number.

Would this do what you need?

Radovan

Anonymous
Not applicable
Author

Hi Radovan,

This should work,

Let me play around and confirm so everyone has the right answer

Regards

Jeba