Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tcybucki
Contributor II
Contributor II

Calculating Time Duration between multiple date stamps across two tables

Hi,

I am trying to analyze how many hours we lose to various types of employee absence.  I have two tables that I have connected by Employee IDs: a Schedule Table and a Leave Table.

The schedule table looks like the following:

Employee ID             Shift Start               Shift End               Location

1                              4/14/2015 00:00       4/14/2015 08:00          A

2                              4/14/2015 10:00       4/14/2015 18:00          A

3                              4/14/2015 16:00       4/15/2015 00:00          B

and so on...we are a 24 hour operation with hundreds of employees, and I have schedule records for each day in the past couple years.

The leave table looks like the following:

Employee ID             Leave Start             Leave End               Leave Type

1                              4/14/2015 00:00       4/14/2015 08:00          Sick

1                              4/18/2015 00:00       4/23/2015 23:59          WCB

2                              4/22/2015 12:00       4/29/2015 18:00          Long Term Medical Leave

and so on...

Employee schedules are fairly consistent over a few weeks, but they drastically change every 4-5 months. The leave table does not follow the same time convention as the schedule table. Generally, long term leave is coded as 00:00 to 23:59 for a date range, and short term (less than 1 day) leave is coded to match an employee's shift. But it can vary wildly given who coded the leave into our scheduling system.

I want to create a table where I can see by day (and by week eventually) how many hours we lose to the various Leave Types. I am having trouble connecting all my timestamps and then calculating hours lost. I was attempting to copy another thread idea where I load each timestamp column into a CommonDate column, but I ended up creating many synthetic keys. I have created Master Calendar's in other apps, but this particular app is the first time I have had four date columns.

I do want to be able to see which employees are on every type of leave, but my main goal is to see how many hours each day/week we lose to various leaves so we can schedule accordingly.

Any help would be greatly appreciated! Thank you!

1 Reply
Gysbert_Wassenaar

This problem is discussed in IntervalMatch and Slowly Changing Dimensions. See page 19 of the pdf file attached to that document.


talk is cheap, supply exceeds demand