Generate missing Dates

[#1]

Hi,

I have following problem:

I have a table like:

Date	ID	Cumulative
01.01.2014	1	1
03.01.2014	1	4
05.01.2014	2	1
07.01.2014	2	3

What I´m trying to achieve is, to generate all missing Dates for the IDs and fill the Rows with the "ID" and "Cumulative" from the  previous "Cumulative" from the ID

So, result should be:

Date	ID	Cumulative
01.01.2014	1	1
02.01.2014	1	4
03.01.2014	1	4
04.01.2014	1	4
05.01.2014	1	4
06.01.2014	1	4
07.01.2014	1	4
01.01.2014	2	0
02.01.2014	2	0
03.01.2014	2	0
04.01.2014	2	0
05.01.2014	2	1
06.01.2014	2	1
07.01.2014	2	3

Thank you for any help

[swuehl · Accepted solution]

You should be able to do it like

INPUT: 
LOAD Date, 
     ID, 
     Cumulative
FROM
[https://community.qlik.com/thread/188863]
(html, codepage is 1252, embedded labels, table is @1);


EXT:
LOAD Min(Date) as Min, Max(Date) as Max
RESIDENT INPUT;


Let vMin = Num(Peek('Min',0,'EXT'));
Let vMax = Num(Peek('Max',0,'EXT'));


RANGE:
LOAD DISTINCT ID Resident INPUT;


JOIN (RANGE)
LOAD Date($(vMin)+Recno()-1) as Date
AutoGenerate $(vMax)-$(vMin)+1;


LEFT JOIN (RANGE) 
LOAD * Resident INPUT;


RESULT:
NOCONCATENATE 
LOAD ID, Date, Rangesum(if(isNull(Cumulative) and Peek(ID) = ID, Peek(Cumulative), Cumulative)) as Cumulative
Resident RANGE
ORDER BY ID, Date;


DROP TABLE EXT, RANGE, INPUT;