Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Generate missing Data

Let´s say i have data like this:

dateid
01.01.20151
03.01.20152
02.01.20151
04.01.20153
05.01.20153

what i´m trying to achieve is to generate all dates between min(date) and max(date) for every id.

so result should look like this:

dateid
01.01.20151
02.01.20151
03.01.20151
04.01.20151
05.01.20151
01.01.20152
02.01.20152
03.01.20152
04.01.20152
05.01.20152
...etc....etc.

Every id shell have a row with every possible date.

How can i achieve this?

many thanks

1 Solution

Accepted Solutions
Highlighted

Re: Generate missing Data

Try this script in that case:

Table:

LOAD Date#(date, 'DD.MM.YYYY') as date

FROM

[https://community.qlik.com/thread/170039]

(html, codepage is 1252, embedded labels, table is @1);

Temp:

LOAD Min(date) as minDate,

  Max(date) as maxDate

Resident Table;

LET vMinDate = Num(Peek('minDate', 0, 'Temp'));

LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Tables Temp, Table;

NewTable:

LOAD

  $(vMinDate) + IterNo() - 1 as Num,

  Date($(vMinDate) + IterNo() - 1) as date

AutoGenerate

  1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Join(NewTable)

LOAD id

FROM

[https://community.qlik.com/thread/170039]

(html, codepage is 1252, embedded labels, table is @1);

View solution in original post

7 Replies
Highlighted
mwoolf
Honored Contributor II

Re: Generate missing Data

Highlighted

Re: Generate missing Data

Hi Steve, you can load each fields in a separate table and make a join:

Date: LOAD Distinct date FROM....;

ID: LOAD Distinct id FROM...;

Table:

LOAD * Resident Date;

Join LOAD * Resident ID;

DROP Tables Date, ID;

That's the explainable version, compacted version can be:

Table:

LOAD Distinct date FROM....;

Join LOAD Distinct id FROM...;

Highlighted

Re: Generate missing Data

Try this:

Table:

LOAD Date#(date, 'DD.MM.YYYY') as date

FROM

[https://community.qlik.com/thread/170039]

(html, codepage is 1252, embedded labels, table is @1);

Join(Table)

LOAD id

FROM

[https://community.qlik.com/thread/170039]

(html, codepage is 1252, embedded labels, table is @1);

Output:

Capture.PNG

Highlighted
Not applicable

Re: Generate missing Data

what if there are some no existing dates?

let´s say there is no entry for date 02.01.2015.

does your solution work then?

Highlighted

Re: Generate missing Data

Try this script in that case:

Table:

LOAD Date#(date, 'DD.MM.YYYY') as date

FROM

[https://community.qlik.com/thread/170039]

(html, codepage is 1252, embedded labels, table is @1);

Temp:

LOAD Min(date) as minDate,

  Max(date) as maxDate

Resident Table;

LET vMinDate = Num(Peek('minDate', 0, 'Temp'));

LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Tables Temp, Table;

NewTable:

LOAD

  $(vMinDate) + IterNo() - 1 as Num,

  Date($(vMinDate) + IterNo() - 1) as date

AutoGenerate

  1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

Join(NewTable)

LOAD id

FROM

[https://community.qlik.com/thread/170039]

(html, codepage is 1252, embedded labels, table is @1);

View solution in original post

Highlighted
MVP
MVP

Re: Generate missing Data

Temp:
Load Date, ID From TableName;

Final:
Load Distinct Date Resident Temp

Join

Load Distinct ID Resident Temp;

Drop Table Temp;

Highlighted

Re: Generate missing Data

Hi Steve, no, it won't create dates that doesn't exists. If you want to generate all dates Sunindia's solution will work.