Finding unique time intervals in case of overlap

[atulkrsingh]

Hi there,

I'm new to Qlik and having trouble with the following dataset:

Patient ID	Appointment Date	Doctor	Start time	End time	Appointment time
1	1-Jan-20	A	9:00 AM	10:00 AM	1:00
2	1-Jan-20	A	11:00 AM	11:30 AM	1:00
3	1-Jan-20	A	11:00 AM	12:15 PM	1:15
4	1-Jan-20	A	12:00 PM	1:00 PM	1:00
5	1-Jan-20	A	4:00 PM	5:00 PM	1:00
6	1-Jan-20	B	10:00 AM	11:30 AM	1:30
7	1-Jan-20	B	4:00 PM	5:00 PM	1:00
8	1-Jan-20	B	4:00 PM	5:00 PM	1:00

I need to find out the actual work time for the practitioners,

Here, 

For, Practitioner A - Total Appointment time = 4:45:00;
                                         Actual Work Time = 4:00:00

For, Practitioner B - Total Appointment time = 3:30:00;
                                         Actual Work Time = 2:30:00

Required Output:

Appointment Date	Doctor	Actual Work Time
1-Jan-20	A	4:00:00
1-Jan-20	B	2:30:00

I was thinking of,
                  Actual Work Time =   Aggr(Sum(Interval( Min([Start Date]) - Max([End Date]))   - (Total time of NO Work ))

However, I'm unsure of how to write the query for Total Time of No Work (i.e., when the Practitioner is not working)

Thanks in Advance for your help

Best Regards,

Atul 

[Saravanan_Desingh]

Your data doesn't show [Total time of NO Work]. Can you add that data to your sample?

[atulkrsingh]

Hi,

[Total time of NO Work] is the time when the practitioner is free. It is something I need to calculate and I'm unable to understand the logic for the same.

Ex- in case of practitioner A,

Time of No work = 1 hr( Between 10:00 AM and 11: AM) + 3 hrs (Between 1 PM to 4PM)

So, the Actual Work Hours = Total Time Available - Time of No work

that is, 8 - 4 hours = 4 hours

[Kushal_Chawda]

for A, total time available isn't 9 (4 Am - 1Pm)>

[atulkrsingh]

Thanks for your response and correcting me Kush,

It was a wrong entry. It will be 4:00 PM to 5:00 PM. I have corrected it.

Hence, Available time is from  9:00 AM to 5:00 PM (8 hours)

[Kushal_Chawda · Accepted solution]

try below

Data:
LOAD
    "Patient ID",
    "Appointment Date",
    Doctor,
    Time#("Start time",'hh:mm TT') as "Start time",
    Time#("End time",'hh:mm TT') as "End time",
    Time#("Appointment time",'hh:mm') as "Appointment time"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);

New:
Load *,
     rangesum(if(Peek(Doctor)=Doctor and Peek("Appointment Date")="Appointment Date", 
     if("Start time">Peek("End time"),
     "Start time"-Peek("End time"),0))) as No_work_Hours
Resident Data
Order by Doctor,"Appointment Date","Start time";

Drop Table Data;

//Now on frond end create table
Dimensions:
1) Appointment Date
2) Doctor

Expression:
1)Actual Work Time
=time(sum(aggr((max([End time])-min([Start time])) -sum(No_work_Hours),[Appointment Date],Doctor)))
2) No Work Time
=time(sum(No_work_Hours))

[atulkrsingh]

Thanks Kush! 🙂

[atulkrsingh]

Appt Date	Doctor	Patient ID	Start time	End time	No Work Time
					1:30
6/16/2020	A	1	8:15:00	9:00:00	0:00
6/16/2020	A	2	9:00:00	10:00:00	0:00
6/16/2020	A	3	9:15:00	9:30:00	0:00
6/16/2020	A	4	10:00:00	11:00:00	0:00
6/16/2020	A	5	10:00:00	10:30:00	0:30
6/16/2020	A	6	11:00:00	12:00:00	0:00
6/16/2020	A	7	13:00:00	14:00:00	1:00
6/16/2020	A	8	14:00:00	14:30:00	0:00
6/16/2020	A	9	14:00:00	14:45:00	0:00
6/16/2020	A	10	14:30:00	15:30:00	0:00
6/16/2020	A	11	14:30:00	15:30:00	0:00

Hi Kush,

While most of the output is correct, I'm getting the wrong output for many such cases (marked in 'red'). Can't figure out why this is happening. Can you please help?

Thanks,

Atul

[Kushal_Chawda]

How you are getting 30 Minutes no work as he already attended Patient from 10 to 11 AM? should it now be 30 minutes for patient id 6?

[atulkrsingh]

That is the error. It should have been "0 mins" instead of 30 min for patient id - 5. There are a few anomalies like this else, the logic you gave works fine for other records. "No work time" for patient id - 6 will be zero and is correct