cancel
Showing results for
Did you mean:
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
MVP

You should be able to do it like

```INPUT:
ID,
Cumulative
FROM
(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;
```
MVP

You should be able to do it like

```INPUT:
ID,
Cumulative
FROM
(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;
```
Community Browser