topic Fill missing dates and use old value in QlikView

Fill missing dates and use old value

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

I have a table as below in the QVW attached.

RAW:

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

];

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.

In my sample QVW, it is failing. Not sure 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.

@sunny_talwar I had seen you solve something similar in the past. Any help is highly appreciated 🙂

Re: Fill missing dates and use old value

zhadrakas — Tue, 28 Jul 2020 08:09:20 GMT

please do not duplicate the same Ticket.

https://community.qlik.com/t5/New-to-QlikView/Fill-missing-dates-and-use-old-value/m-p/1731078#M391561

here are already information about his problem.

Re: Fill missing dates and use old value

sunny_talwar — Wed, 29 Jul 2020 13:19:50 GMT

Try this

RAW: 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 RESIDENT RAW; LET varMinDate = Num(Peek('MinDate',-1,'temp')); LET varMaxDate = Num(Peek('MaxDate',-1,'temp')); drop table temp; //Fill Date Gaps tmp: LOAD date($(varMinDate)+ rowno() -1) AS Date AUTOGENERATE $(varMaxDate)-$(varMinDate)+1; Join (tmp) LOAD ID, Type Resident RAW; Join (RAW) LOAD DISTINCT * Resident tmp; DROP Table tmp; //fill gaps with previous values FINAL: NoConcatenate LOAD Date, ID, Type, If(ID = Peek(ID) and Type = Peek(Type) and IsNull(Value), Peek(Value), Value) as Value Resident RAW Order By ID, Type, Date ASC; DROP Table RAW;