Discussion board where members can get started with QlikView.
I have a table as below in the QVW attached.
LOAD * INLINE [
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 🙂
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
LOAD min(Date) as MinDate,
max(Date) as MaxDate
LET varMinDate = Num(Peek('MinDate',-1,'temp'));
LET varMaxDate = Num(Peek('MaxDate',-1,'temp'));
drop table temp;
//Fill Date Gaps
LOAD date($(varMinDate)+ rowno() -1) AS Date
LOAD DISTINCT *
DROP Table tmp;
//fill gaps with previous values
If(ID = Peek(ID) and Type = Peek(Type) and IsNull(Value), Peek(Value), Value) as Value
Order By ID, Type, Date ASC;
DROP Table RAW;
View solution in original post
please do not duplicate the same Ticket.
here are already information about his problem.