Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
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

4 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

View solution in original post

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