Expand a table to all date of year

[Andrea_001]

Hi at all,

I have a table like this:

tbl01:

ID	VALUE	DATE
23	10	01/02/2018
23	20	02/04/2018

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:

ID	VALUE	DATE
23	10	01/02/2018
23	10	02/02/2018
23	10	03/02/2018
23	10	04/02/2018
23	10	05/02/2018
23	10	06/02/2018
23	10	07/02/2018
23	10	…..
23	20	02/04/2018
23	20	03/04/2018
23	20	04/04/2018
23	20	05/04/2018
23	20	06/04/2018
23	20	07/04/2018
23	20	08/04/2018
23	20	09/04/2018
23	20	…
23	20	31/12/2018

Can someone help me ?

Thank you in advance

Andrea

[Or]

This gets asked... a lot.

https://community.qlik.com/t5/QlikView-Scripting/How-to-fill-missing-values/m-p/333858

https://community.qlik.com/t5/QlikView-App-Development/Fill-in-missing-values/td-p/11407

https://community.qlik.com/t5/QlikView-App-Development/populate-missing-values/td-p/14297

Google for more examples.

[Andrea_001]

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]

That's basically the same thing...

Try Henric's classic post:

https://community.qlik.com/t5/QlikView-Documents/Generating-Missing-Data-In-QlikView/ta-p/1491394

[martinpohl · Accepted solution]

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