topic Re: How to create Timestamps between two Date/Hours in App Development

How to create Timestamps between two Date/Hours

arnould_it — Mon, 15 Jan 2018 12:29:15 GMT

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

Re: How to create Timestamps between two Date/Hours

sunny_talwar — Mon, 15 Jan 2018 12:43:32 GMT

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

maxgro — Mon, 15 Jan 2018 13:12:09 GMT

maybe something like this to create hours between 2 date

// test data

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

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

darrell_tobin — Mon, 15 Jan 2018 13:26:51 GMT

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.

Re: How to create Timestamps between two Date/Hours

arnould_it — Mon, 12 Feb 2018 07:53:03 GMT

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