topic Re: Expand a table to all date of year in App Development

Expand a table to all date of year

Andrea_001 — Wed, 27 May 2020 10:38:44 GMT

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

Or — Wed, 27 May 2020 11:01:13 GMT

This gets asked... a lot.

Google for more examples.

Andrea_001 — Wed, 27 May 2020 11:09:22 GMT

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 — Wed, 27 May 2020 11:22:47 GMT

That's basically the same thing...

Try Henric's classic post:

martinpohl — Wed, 27 May 2020 12:12:27 GMT

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