How get Master Calendar and Master Time in a binnacle

[luisdarwinqlik]

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

[Gysbert_Wassenaar]

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.