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: 
qlikwiz123
Creator III
Creator III

Fill missing dates and use old value

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 🙂

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

2 Replies
zhadrakas
Specialist II
Specialist II

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#M3915...

here are already information about his problem.

 

sunny_talwar

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;