Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am sure this has been solved many times in the past, but I am looking for the simplest method.
I have a DateTime stamp coming from my datasource and I want to split it into a Date dimension and a TimeOfDay (Clock) dimension. The Clock dimension should have one record for every second on the day. This means 86400 rows, from 00:00:00 to 23:59:59.
How would you go about creating this Clock dimension?
I am starting from here:
Tmp_Times:
LOAD
rowno() -1 as SecondOfDay
Autogenerate 86400;
Edit: I am simplifying my source data slightly by stripping out milliseconds using the following function:
MakeTime(Hour(DateRequested), Minute(DateRequested), Second(DateRequested))
That's much better thanks. My full script looks like this:
Tmp_Times:
LOAD
Time((RowNo() - 1) / 86400, 'hh:mm:ss') as Time
Autogenerate 86400;
Clock:
LOAD
MakeTime(Hour(Time), Minute(Time), Second(Time)) as TimeRequested,
Hour(Time) as Hour,
Minute(Time) as Minute,
Second(Time) as Second
Resident Tmp_Times;
DROP Table Tmp_Times;
Message was edited by: Michael Fenton I had to modify slightly as the /86400 on the third row resulted in a level of precision that did not map to the source column
Hi Michael,
You could generate the time values like this:
Tmp_Times:
LOAD
time((rowno() -1)/86400,'hh:mm:ss') as SecondOfDay
Autogenerate 86400;
That's much better thanks. My full script looks like this:
Tmp_Times:
LOAD
Time((RowNo() - 1) / 86400, 'hh:mm:ss') as Time
Autogenerate 86400;
Clock:
LOAD
MakeTime(Hour(Time), Minute(Time), Second(Time)) as TimeRequested,
Hour(Time) as Hour,
Minute(Time) as Minute,
Second(Time) as Second
Resident Tmp_Times;
DROP Table Tmp_Times;
Message was edited by: Michael Fenton I had to modify slightly as the /86400 on the third row resulted in a level of precision that did not map to the source column