Data Autogenerate

[maxsheva]

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.

[vinieme12]

Here is your answer

How to populate a sparsely populated field

[maxgro]

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;