Generate one row per hour in a day

[Anonymous]

Hello,

I need to generate one record per hour for a table that has a column ID and Date of Creation (YYYY-MM-DD format) what is the better way to generate 24 records for each ID in the days when this ID had occurred in the Script?

Regards.

[petter]

Do you want these records (rows) to be generated in a new table or do you want your table to get additional records added? What will happen with the other fields of the table - will the day values be just copied or how do you want them to be handled?

[#3]

Try like below:

// Below generate hour values for 99 days, if you need more change the no after Autogenerate

Data:

Load

     Today() - RecNo() + 1 + ((Iterno()-1)/24) AS DATE_HOUR

Autogenerate 100 while IterNo() <= 24

;

Join(Data)

Load Distinct ID

Resident Transactions;

[MarcoWedel · Accepted solution]

Hi,

maybe like this?

table1:
LOAD *,
    Timestamp([Date of Creation]+(IterNo()-1)/24) as [DateTime of Creation],
    Time((IterNo()-1)/24) as [Time of Creation]
Inline [
ID, Date of Creation
1, 2016-01-19
1, 2016-01-20
2, 2016-01-20
2, 2016-01-21
3, 2016-01-22
]
While IterNo()<=24;

hope this helps

regards

Marco

[Anonymous]

Hi Marco, thanks for your reply,

But i have another problem in my table... I have datetime of creation and datetime of disable, for example:

ID 1

Datetime of Creation: 2016-01-20 06:21:12

Datetime of Disable: 2016-01-20 22:45:10

In this case will be generate 17 rows, one per hour for every hour the ID 1 stay enable, it's possible to utilising the function IterNo() in this case?

[MarcoWedel]

‌yes, it's possible.

please close this thread if your initial question is answered and open another one for this new question.

thanks

regards

Marco