4 Replies Latest reply: Feb 12, 2018 2:53 AM by alessandro trinca RSS

    How to create Timestamps between two Date/Hours

    alessandro trinca

      Hi,

       

      could anyone help me in this?

       

      I've to gather data from an Excel file in which there's just some values, by date and hour.

       

      Loading that data I've to fill all the rows between two date/hours, automatically creating ALL the inter-current date/hours; after do this I've to divide the next value by the inter-current rows count, but this is simple.

       

      The main problem is the first: how to fill all rows creating all the inter-current date/hours.

       

      This is an example:

       

      ExcelTimestampFill.png

       

      Thanks in advance

      Alessandro

        • Re: How to create Timestamps between two Date/Hours
          Sunny Talwar

          Check here on how to create all dates between two dates using while loop...

          Loops in the Script

           

          Same logic will go for DateTimes

          • Re: How to create Timestamps between two Date/Hours
            Massimo Grossi

            maybe something like this to create hours between 2 date

             

            // test data

            S:

            load

                 Date,

                 Floor(rand()*100) as Value1,

                 Floor(rand()*100) as Value2

            inline [

            Date

            18/01/2018 03:00:00

            17/01/2018 03:00:00

            16/01/2018 16:00:00

            16/01/2018 08:00:00

            15/01/2018 22:00:00

            31/12/2017 23:00:00

            ];

             

            // add DateTo

            S1:

            load

                 RowNo() as Id, Value1, Value2,

                 Date, Alt(Peek(Date), Date) as DateTo

            Resident S

            Order By Date desc;

            DROP Table S;

             

            // add Hours between Date....DateTo

            F:

            load

                 *,

                 Timestamp(Date + (IterNo()-1) / (24)) as DateTime

            Resident S1

            While (Date + (IterNo()-1) / (24)) <= (DateTo);

            DROP Table S1;

              • Re: How to create Timestamps between two Date/Hours
                alessandro trinca

                Hi Massimo,

                thanks for the suggestion.

                 

                Just one last question: how can I split values dividing them by the number of Days/Hours Between two dates, using your solution?

                 

                Example:

                Date

                05/02/2018  Value 1=1000  Value2=1500

                 

                Previous Date:

                28/01/2018 Value 1:500 Value 2= 800

                 

                I've to divide the last Values (1000 and 1500) splitting them for all Days and Hours between 29/01/2018 (the first missed date after 28/01/2018) and 05/02/2018.

                 

                Thanks in advance

                Alessandro

              • Re: How to create Timestamps between two Date/Hours
                Darrell Tobin

                Hi Alessandro,

                Step 1

                I would add a data hour table with ALL dates and hours over the report measurement period.

                Table name = T_DateHour

                Field Name = DateHour

                Field Example = 01 jan 2017 14:00,01 jan 2017 15:00

                Step 2

                Create same field in your current data

                Field Name = DateHour

                Field Example = 01 jan 2017 14:00,01 jan 2017 15:00

                 

                The new table should then key into your old data using the DateHour field and your report will have the date hour field available to select.

                 

                Hope this helps

                 

                Darrell

                Please mark as correct or helpful if applicable.