Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Station | Date | Price |
---|---|---|
A | 2017-01-01 | 1.500 |
A | 2017-01-04 | 1.550 |
A | 2017-01-08 | 1.488 |
B | 2017-01-03 | 1.300 |
B | 2017-01-08 | 1.305 |
And I would like to fill in the missing rows to get the following result:
Station | Date | Price |
---|---|---|
A | 2017-01-01 | 1.500 |
A | 2017-01-02 | 1.500 |
A | 2017-01-03 | 1.500 |
A | 2017-01-04 | 1.550 |
A | 2017-01-05 | 1.550 |
A | 2017-01-06 | 1.550 |
A | 2017-01-07 | 1.550 |
A | 2017-01-08 | 1.488 |
B | 2017-01-03 | 1.300 |
B | 2017-01-04 | 1.300 |
B | 2017-01-05 | 1.300 |
B | 2017-01-06 | 1.300 |
B | 2017-01-07 | 1.300 |
B | 2017-01-08 | 1.305 |
So, I want to fill in rows between the original data to have a row for each day. But how?
Thanks!
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
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
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!
Change statement with
LOAD Station,Price,Date,Date(If(Station=Peek(Station),Peek(Date),Today())) as Date1
Once again, thank you
this is great. thank you. And what if I wanted to fill in dates before 2017-01-01?