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: 
Not applicable

Filling in the missing data

Hi guys,

I have read:

How to populate a sparsely populated field

Generating Missing Data In QlikView

I have an extra dimension "Station" and I struggle to find out how to make the linked articles apply to this problem:

My table looks like:

StationDatePrice
A2017-01-011.500
A2017-01-041.550
A2017-01-081.488
B2017-01-031.300
B2017-01-081.305

And I would like to fill in the missing rows to get the following result:

StationDatePrice
A2017-01-011.500
A2017-01-021.500
A2017-01-031.500
A2017-01-041.550
A2017-01-051.550
A2017-01-061.550
A2017-01-071.550
A2017-01-081.488
B2017-01-031.300
B2017-01-041.300
B2017-01-051.300
B2017-01-061.300
B2017-01-071.300
B2017-01-081.305

So, I want to fill in rows between the original data to have a row for each day. But how?

Thanks!

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi Rasmus,

maybe this

Temp:
LOAD Station, Date,Price
FROM
https://community.qlik.com/message/1352382
(html, codepage is 1252, embedded labels, table is @1);
  Temp1:
LOAD Station,Price,Date,Date(If(Station=Peek(Station),Peek(Date),Date+1)) as Date1
Resident Temp
Order By Station,Date Desc;
NoConcatenate LOAD
Station,Price,
If(Date1 > Date,Date(Date+IterNo()-1)) as Date
Resident Temp1
While Date+IterNo()-1 < Date1
;
Drop Table Temp,Temp1;

Regards,

Antonio

View solution in original post

5 Replies
antoniotiman
Master III
Master III

Hi Rasmus,

maybe this

Temp:
LOAD Station, Date,Price
FROM
https://community.qlik.com/message/1352382
(html, codepage is 1252, embedded labels, table is @1);
  Temp1:
LOAD Station,Price,Date,Date(If(Station=Peek(Station),Peek(Date),Date+1)) as Date1
Resident Temp
Order By Station,Date Desc;
NoConcatenate LOAD
Station,Price,
If(Date1 > Date,Date(Date+IterNo()-1)) as Date
Resident Temp1
While Date+IterNo()-1 < Date1
;
Drop Table Temp,Temp1;

Regards,

Antonio

Not applicable
Author

Thank you, that works as described.

Any chance that the script can be modified easily to extend data with rows until todays date? So, for Station A, there would be rows from 2017-01-08 and until today with value 1.488.

Thank again!

antoniotiman
Master III
Master III

Change statement with

LOAD Station,Price,Date,Date(If(Station=Peek(Station),Peek(Date),Today())) as Date1

Not applicable
Author

Once again, thank you

trmarsha
Partner - Contributor
Partner - Contributor

this is great. thank you. And what if I wanted to fill in dates before 2017-01-01?