Generate missing Dates

Fri, 23 Oct 2015 08:51:20 GMT

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

Re: Generate missing Dates

swuehl — Fri, 23 Oct 2015 15:28:54 GMT

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;

topic Re: Generate missing Dates in QlikView

Generate missing Dates

Re: Generate missing Dates