
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How get Master Calendar and Master Time in a binnacle
Hi Experts,
My doubt raisen when I was make a chart with hours.
I have a binnacle when I place all my activities day after day as follow:
Id Task
Task
Start Date
Start Time
End Date
End Time
In script I got to merge [Start Date] & [Start Time] as Start, and [End Date] & [End Time] as End.
The new fields have format: 'YYYY-MM-DD hh:mm:ss tt'
Basically that's my layout, I'm looking to measure end to end my time by Year, Month, Day, Hour, Minute...
First I would like to make a Master Calendar to include my Start and End in a same dimension, and next that I need a similar table to link the time and make charts, for instance a bar chart where I can show from start to end of my binnacle by hour o ranges of time.
ID Task | Task | Start Date | Start Time | End Date | End Time |
RzLs_0000000001 | Help Desk | 20181211 | 14:44:00 | 20181211 | 14:44:07 |
RzLs_0000000002 | Duties MLW | 20181211 | 14:44:07 | 20181211 | 14:44:14 |
RzLs_0000000003 | Analyse | 20181211 | 14:44:14 | 20181211 | 14:44:19 |
RzLs_0000000004 | Program | 20181211 | 14:44:19 | 20181212 | 15:24:03 |
RzLs_0000000005 | Mistake last process | 20181212 | 15:24:03 | 20181212 | 15:36:46 |
RzLs_0000000006 | Meal Break | 20181212 | 15:36:46 | 20181212 | 15:44:43 |
RzLs_0000000007 | Analyse | 20181212 | 15:44:43 | 20181212 | 15:44:56 |
RzLs_0000000008 | Execute Tests | 20181212 | 15:44:56 | 20181213 | 09:03:02 |
RzLs_0000000009 | Execute Tests | 20181213 | 09:03:02 | 20181213 | 09:04:09 |
RzLs_0000000010 | Execute Tests | 20181213 | 09:04:09 | 20181213 | 09:05:04 |
RzLs_0000000011 | Analyse | 20181213 | 09:05:04 | 20181213 | 09:07:16 |
RzLs_0000000012 | Execute Tests | 20181213 | 09:07:16 | 20181213 | 10:31:50 |
RzLs_0000000013 | Training | 20181213 | 10:31:50 | 20181213 | 10:55:24 |
RzLs_0000000014 | Comfort Break | 20181213 | 10:55:24 | 20181213 | 11:34:54 |
RzLs_0000000015 | Meeting | 20181213 | 11:34:54 | 20181213 | 14:33:33 |
RzLs_0000000016 | Training | 20181213 | 14:33:33 | 20181213 | 14:33:45 |
RzLs_0000000017 | Meal Break | 20181213 | 14:33:45 | 20181213 | 14:39:06 |
RzLs_0000000018 | Meeting | 20181213 | 14:39:06 | 20181213 | 14:48:08 |
RzLs_0000000019 | Meeting | 20181213 | 14:48:08 | 20181213 | 14:48:49 |
RzLs_0000000020 | Meal Break | 20181213 | 14:48:49 | 20181213 | 14:49:14 |
RzLs_0000000021 | Duties MLW | 20181213 | 14:49:14 | 20181213 | 15:56:31 |
RzLs_0000000022 | Program | 20181213 | 15:56:31 | 20181213 | 16:30:31 |
RzLs_0000000023 | Reporting | 20181214 | 07:38:21 | 20181214 | 09:29:39 |
RzLs_0000000024 | Advising | 20181214 | 09:29:39 | 20181214 | 09:29:51 |
RzLs_0000000025 | Analyse | 20181214 | 09:29:51 | 20181214 | 09:33:55 |
RzLs_0000000026 | Mistake last process | 20181214 | 09:33:55 | 20181214 | 11:35:17 |
RzLs_0000000027 | Design | 20181214 | 11:35:17 | 20181214 | 12:18:52 |
RzLs_0000000028 | Meal Break | 20181214 | 12:18:52 | 20181214 | 13:08:39 |
RzLs_0000000029 | Advising | 20181214 | 13:08:39 | 20181214 | 13:09:50 |
RzLs_0000000030 | Advising | 20181214 | 13:09:50 | 20181214 | 13:10:16 |
RzLs_0000000031 | Call | 20181214 | 13:10:16 | 20181214 | 13:12:01 |
RzLs_0000000032 | 20181214 | 13:12:01 | 20181214 | 13:13:23 | |
RzLs_0000000033 | Advising | 20181214 | 13:13:23 | 20181214 | 13:14:00 |
RzLs_0000000034 | Meeting | 20181214 | 13:14:00 | 20181214 | 14:14:09 |
RzLs_0000000035 | Execute Tests | 20181214 | 14:14:09 | 20181214 | 14:14:35 |
RzLs_0000000036 | Help Desk | 20181214 | 14:14:35 | 20181214 | 14:14:56 |
RzLs_0000000037 | WebEx Meeting | 20181214 | 14:14:56 | 20181214 | 14:52:24 |
RzLs_0000000038 | Program | 20181214 | 14:52:24 | 20181214 | 15:13:26 |
RzLs_0000000039 | Work Session | 20181214 | 15:13:26 | 20181217 | 07:40:08 |
I hope be helped with that, I have a several days trying to find a solve, but sadly I couldn't.
Regards
Luis

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Perhaps like this:
LinkTable:
LOAD DISTINCT
[ID Task],
Date(Floor(DateTime)) as Date,
Hour(DateTime) as Hour
; LOAD [ID Task], FLoor([Start] + (IterNo() - 1)/24,1/24) as DateTime RESIDENT TaskDataTable WHILE [Start] + (IterNo() - 1)/24 <= [End] ; MasterCalendar: LOAD DISTINCT Date, Year(Date) as Year, Month(Date) as Month, ...other fields derived from Date... RESIDENT LinkTable ;
The above doesn't create a separate Master Time table. The Hour field is in the LinkTable table. If you want you can create an extra Master Time table using that field.
talk is cheap, supply exceeds demand
