Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Something like this
If(Frac(TimeStampField) < MakeTime(7), Dual(1, '<7'),
If(Frac(TimeStampField) > MakeTime(18), Dual(2, '>6'), Dual(3, '>=7<=6')
)
)
Are you looking to divide them in multiple rows? or are you looking to flag them in the same row?
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.
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?
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 |
Something like this
If(Frac(TimeStampField) < MakeTime(7), Dual(1, '<7'),
If(Frac(TimeStampField) > MakeTime(18), Dual(2, '>6'), Dual(3, '>=7<=6')
)
)
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.
You can create a master time table.