Solved: Expand a table to all date of year - Qlik Community

Andrea_001 · ‎2020-05-27

Hi at all,

I have a table like this:

tbl01:

I'd like to 'expand' it to another table where the ID and VALUE is replicated for all date, until next change of date, like this:

tbl02:

Can someone help me ?

Thank you in advance

Andrea

martinpohl · ‎2020-05-27

here's a script

Data:
LOAD * INLINE [
ID, VALUE, DATE,
23, 10, 01/02/2018
23, 20, 02/04/2018
];

Temp:

load
date(rowno() + num('01/02/2018') -1) as DATE
AutoGenerate (num('02/04/2018')-num('01/02/2018')+1);

left join load Distinct ID
resident Data;

outer join (Data) load * resident Temp;

drop table Temp;

Sort:
NoConcatenate
load
ID,
VALUE,
DATE
resident Data
order by ID, DATE;

drop table Data;

Addeddata:
NoConcatenate
load
if(isnull(VALUE) and ID = Previous(ID) ,peek('VALUE',-1),VALUE) as VALUE,
ID,
DATE
resident Sort;

drop table Sort;

Regards

Martin

Or · ‎2020-05-27

This gets asked... a lot.

Google for more examples.

Andrea_001 · ‎2020-05-27

Thanks,

my problem is not filling missing values, but generate new record date between two existing date

I've searched but I don't find a solution

Or · ‎2020-05-27

That's basically the same thing...

Try Henric's classic post:

martinpohl · ‎2020-05-27