Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Generate missing Dates

Hi,

I have following problem:

I have a table like:

DateIDCumulative
01.01.201411
03.01.201414
05.01.201421
07.01.201423

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:

DateIDCumulative
01.01.201411
02.01.201414
03.01.201414
04.01.201414
05.01.201414

06.01.2014

14
07.01.201414
01.01.201420
02.01.201420
03.01.201420
04.01.201420
05.01.201421
06.01.201421
07.01.201423

Thank you for any help

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Generate missing Dates

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;

View solution in original post

1 Reply
Highlighted
MVP
MVP

Re: Generate missing Dates

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;

View solution in original post