Generate missing Data

[#1]

Let´s say i have data like this:

date	id
01.01.2015	1
03.01.2015	2
02.01.2015	1
04.01.2015	3
05.01.2015	3

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:

date	id
01.01.2015	1
02.01.2015	1
03.01.2015	1
04.01.2015	1
05.01.2015	1
01.01.2015	2
02.01.2015	2
03.01.2015	2
04.01.2015	2
05.01.2015	2
...etc.	...etc.

Every id shell have a row with every possible date.

How can i achieve this?

many thanks

[m_woolf]

Generating Missing Data In QlikView

[rubenmarin]

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...;

[sunny_talwar]

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:

[#5]

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?

[sunny_talwar · Accepted solution]

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);

[MK_QSL]

Temp:
Load Date, ID From TableName;

Final:
Load Distinct Date Resident Temp

Join

Load Distinct ID Resident Temp;

Drop Table Temp;

[rubenmarin]

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