topic Fill the missing dates with the Previous days Value in QlikView

Fill the missing dates with the Previous days Value

Anonymous — Mon, 05 Nov 2018 21:58:06 GMT

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

Re: Fill the missing dates with the Previous days Value

vishsaggi — Mon, 05 Nov 2018 22:10:43 GMT

Can you give an example output you are looking based on your sample attached?

Re: Fill the missing dates with the Previous days Value

Anonymous — Mon, 05 Nov 2018 22:16:09 GMT

Hi Vishwarth,

Thanks for the quick reply.

Please refer the highlighted rows in yellow.

Those are the missing dates in the source and I want the data to be copied from 9/26 till 9/29 from the source data of 9/25.

Again this should be dynamic logic for all such missing data.

Regards

Jeba

Re: Fill the missing dates with the Previous days Value

Anonymous — Tue, 06 Nov 2018 14:26:15 GMT

Can someone guide me with the right path?

Re: Fill the missing dates with the Previous days Value

RadovanOresky — Tue, 06 Nov 2018 15:33:02 GMT

Hi,

I would approach it in two steps (in the script):

1. join all dates (your field value is PERIOD) to the fact table which has the ID and Type fields

2. use Peek() function to fetch the value of the previous record for the Limit and Required; in this Resident load you need not to forget the Order By statement - in your case it will be something like Order By ID, Type, PERIOD asc

Hope this helps,

Radovan

Re: Fill the missing dates with the Previous days Value

Anonymous — Tue, 06 Nov 2018 19:02:13 GMT

This is what I am trying,but didn't work

LOAD ID,

PERIOD,

Type,

Limit,

Required

//ID&PERIOD&Type&Limit&Required AS KEY

FROM

[Sample.xlsx]

(ooxml, embedded labels, table is Sample);

NOCONCATENATE

TABLE:

LOAD

DATE (FLOOR (PERIOD), 'YYYYMMDD') AS KEY_PERIOD,

DATE (FLOOR (PERIOD), 'YYYYMMDD') AS EFFECTIVE_DATE

RESIDENT INLINE;

DROP TABLE INLINE;

MinMax: // make min and max for every ID

load

ID,

//KEY,

Type,

Limit,

Required,

min(PERIOD) as MinPeriod,

today()-1 as MaxPeriod

Resident TABLE

group by ID,Type,Limit,Required;

TABLE2: // all required date between min and max (for every ID)

Load

ID,

// Type,

// Limit,

// Required,

date(MinPeriod -1 + IterNo()) as PERIOD

Resident MinMax

While IterNo() <= MaxPeriod - MinPeriod +1;

DROP Table MinMax;

Left join (TABLE2) // join with source table (in case you have to add some field from source)

load *

//if(len(trim([Type]))=0,Previous([Type]),[Type]) as [Type1]

resident TABLE;

DROP Table TABLE;

RENAME table TABLE2 to TABLE;

NoConcatenate

FINAL:

Load

PERIOD,

ID,

If(LEN(TRIM(Type))=0 AND PERIOD = Peek(PERIOD),Peek(Type),Type) as Type,

If(LEN(TRIM(Limit))=0,Peek(Limit),Limit) as Limit,

If(LEN(TRIM(Required))=0,Peek(Required),Required) as Required

//and Limit= peek(Limit) and Limit= peek(Limit), Peek(Warehouse), Warehouse) as Warehouse

Resident TABLE Order by PERIOD,ID;

DROP TABLE TABLE;

Re: Fill the missing dates with the Previous days Value

Anonymous — Tue, 06 Nov 2018 20:56:27 GMT

Did you had a chance to review the sample output?

Re: Fill the missing dates with the Previous days Value

vishsaggi — Tue, 06 Nov 2018 21:36:21 GMT

Sorry Dass, was super busy with some stuff so could not spend time. will check tomorrow if thats ok?

Re: Fill the missing dates with the Previous days Value

MarcoWedel — Tue, 06 Nov 2018 21:43:32 GMT

maybe helpful:

Generating Missing Data In QlikView

Re: Fill the missing dates with the Previous days Value

Anonymous — Wed, 07 Nov 2018 01:17:16 GMT

Hi,

Please try this,

If(isnull(date),previous(date),date)

Hope this helps

Re: Fill the missing dates with the Previous days Value

Anonymous — Wed, 07 Nov 2018 01:20:12 GMT

Hi Marco,

Unfortunately this is not working in my case

Re: Fill the missing dates with the Previous days Value

Anonymous — Wed, 07 Nov 2018 01:20:38 GMT

No this doesn't work.

I already tested multiple options

Re: Fill the missing dates with the Previous days Value

Anonymous — Wed, 07 Nov 2018 01:21:44 GMT

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

Re: Fill the missing dates with the Previous days Value

Anonymous — Wed, 07 Nov 2018 19:32:07 GMT

Sure. no issues

Re: Fill the missing dates with the Previous days Value

RadovanOresky — Thu, 08 Nov 2018 15:08:57 GMT

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

Re: Fill the missing dates with the Previous days Value

Anonymous — Thu, 08 Nov 2018 15:43:56 GMT

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?

Re: Fill the missing dates with the Previous days Value

RadovanOresky — Thu, 08 Nov 2018 17:29:27 GMT

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

Re: Fill the missing dates with the Previous days Value

Anonymous — Thu, 08 Nov 2018 18:45:49 GMT

Hi Radovan,

This should work,

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

Regards

Jeba