Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Clock Dimension

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

1 Solution

Accepted Solutions
Not applicable

Re: Clock Dimension

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

2 Replies
jsn
Honored Contributor

Re: Clock Dimension

Hi Michael,

You could generate the time values like this:

Tmp_Times:

LOAD

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

Autogenerate 86400;

Not applicable

Re: Clock Dimension

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

Community Browser