Cuting Times in timeseries-dimension

[george55]

Hi all,

Have a Timestamp dimension with 24 hours data. Would like to cut all Times before 7 a.m. and after 6 p.m. in the load script. What is the best approach? Do a For loop, or with joins and creating a new table or with if's or other idea?

thank you.

George

[sunny_talwar]

Are you looking to divide them in multiple rows? or are you looking to flag them in the same row?

[george55]

Both variants are ok. If I have a flag, I could slice them with set analysis in the app-objects. Regarding performance, don't know what is better, but with a flag I have more possibilities.

[sunny_talwar]

I agree, so your data is like this, right?

TimeField
12:27
16:23
4:02
22:01

and you just want to identify rows where the time is less than 7 and more than 18, right? 

[george55]

there timestamps like this:

2018-05-23 11:03:26.326326

2018-05-23 11:03:26.326326

2018-05-23 11:03:44.344344

2018-05-23 11:03:44.344344

2018-05-23 11:03:44.344344

2018-05-23 11:03:45.345345

2018-05-24 14:18:25.18251825

2018-05-29 14:58:00.580580

2018-05-29 16:56:19.56195619

2018-05-29 16:56:19.56195619

2018-05-29 16:56:26.56265626

2018-05-29 16:56:26.56265626

2018-05-29 16:56:26.56265626

2018-05-29 16:56:29.56295629

2018-05-29 16:56:29.56295629

2018-05-29 16:56:29.56295629

2018-05-29 16:56:38.56385638

2018-05-29 16:56:38.56385638

2018-05-31 07:56:32.56325632

2018-05-31 09:53:52.53525352

2018-05-31 11:41:54.41544154

2018-05-31 13:20:27.20272027

2018-06-04 08:15:35.15351535

2018-06-04 08:40:07.407407

2018-06-05 09:25:46.25462546

2018-06-05 09:29:19.29192919

[sunny_talwar · Accepted solution]

Something like this

If(Frac(TimeStampField) < MakeTime(7), Dual(1, '<7'),
    If(Frac(TimeStampField) > MakeTime(18), Dual(2, '>6'), Dual(3, '>=7<=6')
    )
)

[george55]

Thanks, it worked.

Additional:
As I have many different Timestamps-Dimension, can I use something globaly? Have a global calendar with dates, maybe I can use it? Otherwise I have to put the same code into many Tables. 

[sunny_talwar]

You can create a master time table.