topic Clock Dimension in QlikView

Clock Dimension

Thu, 21 Jun 2012 04:50:34 GMT

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))

Re: Clock Dimension

Anonymous — Thu, 21 Jun 2012 05:18:34 GMT

Hi Michael,

You could generate the time values like this:

Tmp_Times:

LOAD

time((rowno() -1)/86400,'hh:mm:ss') as SecondOfDay

Autogenerate 86400;

Re: Clock Dimension

Thu, 21 Jun 2012 05:24:49 GMT

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