topic Re: Data Autogenerate in QlikView

Data Autogenerate

maxsheva — Sat, 08 Apr 2017 17:57:54 GMT

Hi,

Table1:

LOAD * INLINE [

Example, Date, Value1, Value2

Example1, 01/02/2017, 70, 70

Example1, 01/04/2017, 80, 80

Example1, 01/07/2017, 50, 30

Example1, 01/09/2017, 30, 50

];

How can I generate missing dates with values?

As result I need:

Table1:

LOAD * INLINE [

Example, Date, Value1, Value2

Example1, 01/02/2017, 70, 70

Example1, 01/03/2017, 70, 70

Example1, 01/04/2017, 80, 80

Example1, 01/05/2017, 80, 80

Example1, 01/06/2017, 80, 80

Example1, 01/07/2017, 50, 30

Example1, 01/08/2017, 50, 30

Example1, 01/09/2017, 30, 50

];

Thx.

Re: Data Autogenerate

vinieme12 — Sat, 08 Apr 2017 19:26:26 GMT

Here is your answer

How to populate a sparsely populated field

Re: Data Autogenerate

maxgro — Sat, 08 Apr 2017 22:48:29 GMT

SET DateFormat='MM/DD/YYYY';

// I added some test data, Example2

Table1:

LOAD * INLINE [

Example, Date, Value1, Value2

Example1, 01/02/2017, 70, 70

Example1, 01/04/2017, 80, 80

Example1, 01/07/2017, 50, 30

Example1, 01/09/2017, 30, 50

Example2, 01/04/2017, 70, 70

Example2, 02/04/2017, 80, 80

];

tmp:

LOAD

Example,

Date(min(Date)) as MinDate,

Date(max(Date)) as MaxDate

Resident Table1

Group By Example;

Join (Table1)

LOAD

Example,

Date(MinDate + IterNo() -1) as Date

Resident tmp

While Date(MinDate + IterNo() -1) <= MaxDate;

DROP Table tmp;

Table2:

NoConcatenate LOAD

Example, Date,

Alt(Value1, Peek('Value1')) as Value1,

Alt(Value2, Peek('Value2')) as Value2

Resident Table1

Order By Example, Date;

DROP Table Table1;