2 Replies Latest reply: Apr 8, 2017 6:48 PM by Massimo Grossi RSS

    Data Autogenerate

    Max Shevchenko

      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
          Massimo Grossi



          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;