Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
Honored Contributor III

Re: Filling in the missing data

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

4 Replies
antoniotiman
Honored Contributor III

Re: Filling in the missing data

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

Re: Filling in the missing data

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
Honored Contributor III

Re: Filling in the missing data

Change statement with

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

Not applicable

Re: Filling in the missing data

Once again, thank you

Community Browser