topic Re: Fill missing dates and use old value in QlikView

Fill missing dates and use old value

qlikwiz123 — Sat, 16 Nov 2024 00:15:59 GMT

Hi,

I have dates and values associated as shown below

Date Value

07/23/2020 – 1000

07/24/2020 – 1200

07/27/2020 – 800

Is there a way to fill the missing dates and use the same value prior to that?

For example, generate missing dates 7/25, 7/26 and show the previous value from 7/24, which is 1200

Output:

Date Value

07/23/2020 – 1000

07/24/2020 – 1200

07/25/2020 – 1200

7/26/2020 - 1200

7/27/2020 - 800

Re: Fill missing dates and use old value

zhadrakas — Fri, 24 Jul 2020 07:00:57 GMT

this should do it

RAW: Load 1 as Dim, MakeDate(2020, 7, 23) as Date, 1000 as Value AutoGenerate(1); Load 1 as Dim, MakeDate(2020, 7, 29) as Date, 1200 as Value AutoGenerate(1); Load 1 as Dim, MakeDate(2020, 8, 27) as Date, 800 as Value AutoGenerate(1); //Get min & Max Date temp: LOAD min(Date) as MinDate, max(Date) as MaxDate RESIDENT RAW; LET varMinDate = Num(Peek('MinDate',-1,'temp')); LET varMaxDate = Num(Peek('MaxDate',-1,'temp')); drop table temp; //Fill Date Gaps join (RAW) LOAD date($(varMinDate)+ rowno() -1) AS Date AUTOGENERATE $(varMaxDate)-$(varMinDate)+1; //fill gaps with previous values FINAL: NoConcatenate Load Date, If( IsNull( Value ), Peek( Value ), Value ) as Value Resident RAW order by Date ASC; drop table RAW;

Re: Fill missing dates and use old value

qlikwiz123 — Fri, 24 Jul 2020 15:23:24 GMT

Hi,

@zhadrakas

In my sample QVW, it is failing. Can you please tell me what's wrong here? I also have ID and Type as additional fields and maybe that's breaking your logic.

I may also add more fields in the future, so don't want this logic to break if I add new fields.

Re: Fill missing dates and use old value

zhadrakas — Mon, 27 Jul 2020 05:08:23 GMT

change the last part of the script like this.

maybe you want to add ID or Type in order by statement if you want to change the logic.

FINAL: NoConcatenate Load Date, If( IsNull( Value ), Peek( Value ), Value ) as Value, If( IsNull( ID ), Peek( ID ), ID ) as ID, If( IsNull( Type ), Peek( Type ), Type ) as Type Resident RAW order by Date ASC; drop table RAW;

regards

Re: Fill missing dates and use old value

qlikwiz123 — Mon, 27 Jul 2020 14:27:00 GMT

Hi @zhadrakas

Still does not work. Please look at the QVW attached.

Re: Fill missing dates and use old value

zhadrakas — Tue, 28 Jul 2020 06:09:58 GMT

please explain in detail what is not working.

i've downloaded your qvw and everything works as expected. there is no gap in the data model.

regards

Re: Fill missing dates and use old value

qlikwiz123 — Tue, 28 Jul 2020 14:48:38 GMT

If you look at these instances, the dates aren't populated for every ID for every Type.

Re: Fill missing dates and use old value

zhadrakas — Wed, 29 Jul 2020 06:23:50 GMT

yes thats different to the first approach.

can you please show me a sample with your desired output?

the problem with that is that if i change the logic to fill up all "Date-ID-Type-combinations, it will add more datasets than just the gaps of the dates that are not existing.

if there are more columns in future that are important for you, please add them right now.

regards

Re: Fill missing dates and use old value

qlikwiz123 — Wed, 29 Jul 2020 14:02:12 GMT

Hi,

For every ID-Type-Date-Value row, I want all the dates to be generated and for the missing Value, it should pick up the last Value and repeat it until a new date with value is present.

Present:

Desired Output:

Date	ID	Type	Value
7/23/20202	15	Cups	200
7/24/2020	15	Cups	150
7/25/2020	15	Cups	150
7/26/2020	15	Cups	150
7/27/2020	15	Cups	500

Similarly, for other IDs and Type

Re: Fill missing dates and use old value

Kushal_Chawda — Wed, 29 Jul 2020 15:32:21 GMT

RAW: LOAD *, AutoNumberHash256(Date&ID&Type) as Key; LOAD * INLINE [ Date,ID,Type,Value 07/23/2020,11,Plates,1000 07/24/2020,11,Plates,1200 7/27/2020,11,Plates,1000 07/23/2020,11,Cups,800 07/24/2020,11,Cups,1200 07/27/2020,11,Cups,500 07/23/2020,15,Plates,850 07/24/2020,15,Plates,500, 07/25/2020,15,Plates,700 7/27/2020,15,Plates,1000 07/23/2020,15,Cups,200 07/24/2020,15,Cups,150 07/27/2020,15,Cups,500 ]; //Get min & Max Date temp: LOAD min(Date) as MinDate, max(Date) as MaxDate; LOAD FieldValue('Date',RecNo()) as Date AutoGenerate FieldValueCount('Date'); LET varMinDate = Num(Peek('MinDate',-1,'temp')); LET varMaxDate = Num(Peek('MaxDate',-1,'temp')); drop table temp; //Fill Date Gaps NoConcatenate Full: LOAD date($(varMinDate)+ rowno() -1) AS Date AUTOGENERATE $(varMaxDate)-$(varMinDate)+1; Join(Full) LOAD Distinct ID Resident RAW; Join(Full) LOAD Distinct Type Resident RAW; Full_Key: LOAD *, AutoNumberHash256(Date&ID&Type) as Key1 Resident Full; DROP Table Full; Concatenate(RAW) LOAD * Resident Full_Key where not Exists(Key,Key1); DROP Table Full_Key; //fill gaps with previous values FINAL: NoConcatenate Load Date, ID, Type, If( IsNull(Value),Peek(Value),Value) as Value Resident RAW order by ID,Type,Date; drop table RAW;

see the attached

Re: Fill missing dates and use old value

qlikwiz123 — Wed, 29 Jul 2020 18:50:05 GMT

Hi @Kushal_Chawda , thank you so much.

With a little change in the expression

from

     If( IsNull(Value),Peek(Value),Value) as Value

If(ID = Peek(ID) and Type = Peek(Type) and IsNull(Value), Peek(Value), Value) as Value

I got this working with my actual data. Thanks @sunny_talwar for the expression. Also, @zhadrakas thank you for all the help. Every bit helped and is highly appreciated.

Re: Fill missing dates and use old value

Kushal_Chawda — Wed, 29 Jul 2020 19:34:31 GMT

@qlikwiz123 I don't think you need that expression as I have already sorted table on final load so expression i used should work. but anyways glad that it was helpful