topic Re: Generate missing Data in QlikView

Generate missing Data

Thu, 25 Jun 2015 15:17:22 GMT

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

Re: Generate missing Data

m_woolf — Thu, 25 Jun 2015 15:25:48 GMT

Generating Missing Data In QlikView

Re: Generate missing Data

rubenmarin — Thu, 25 Jun 2015 15:28:46 GMT

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

Re: Generate missing Data

sunny_talwar — Thu, 25 Jun 2015 15:30:17 GMT

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:

Re: Generate missing Data

Thu, 25 Jun 2015 15:34:39 GMT

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?

Re: Generate missing Data

sunny_talwar — Thu, 25 Jun 2015 15:38:56 GMT

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

Re: Generate missing Data

MK_QSL — Thu, 25 Jun 2015 15:50:19 GMT

Temp:
Load Date, ID From TableName;

Final:
Load Distinct Date Resident Temp

Join

Load Distinct ID Resident Temp;

Drop Table Temp;

Re: Generate missing Data

rubenmarin — Fri, 26 Jun 2015 06:42:28 GMT

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