Create a Time Dimension based on Work Shifts

[rustyfishbones]

Hi All,

I have an issue where I am trying to create a new Dimension.

Basically, I have work shifts that start at 7am to 7pm (DayShift) and 7pm to 7am (NightShift) continuously.

I want to create a new dimension based on a Time Field that already exists in my Data Model.

How can I create a dimension, based on the Time Field to show transactions that have occurred in a certain shift.

Regards

Alan

[maxgro · Accepted solution]

if you have a timestamp field ts

you can add a new field (the dimension) in bold

😧

load

  rowno() as id,

  timestamp(makedate(2015) + rowno()-1 + rand()) as ts

AutoGenerate 365*2;

Left Join (D)

LOAD

  id,

  if(frac(ts)>= 7/24 and frac(ts)<=19/24, 'DayShift', 'NightShift') as DNShift

Resident D;

[#3]

Hi Alan,

You can try like these

LOAD

WORK SHIFTS,

DayShift(WORK SHIFTS) as 7AM to 7PM

NightShift(WORK SHIFTS) as 7PM to 7AM

FROM YOURTABLE;

[rustyfishbones]

Perfect!!