Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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
Highlighted

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
Highlighted
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.

 

Highlighted

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