How get Master Calendar and Master Time in a binnacle

luisdarwinqlik — Sat, 16 Nov 2024 04:53:39 GMT

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

Re: How get Master Calendar and Master Time in a binnacle

Gysbert_Wassenaar — Thu, 27 Dec 2018 10:51:03 GMT

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.

topic How get Master Calendar and Master Time in a binnacle in QlikView

How get Master Calendar and Master Time in a binnacle

Re: How get Master Calendar and Master Time in a binnacle