How to create Timestamps between two Date/Hours

[arnould_it]

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:

Thanks in advance

Alessandro

[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

[maxgro]

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;

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

[arnould_it]

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