Overlapping Appointments

[BillC]

Hello,

I have a table of clinic encounters containing encounter date, attending physician, start date/time and end date/time (dtm).  Ideally, I would like to add a column via the loading script to record how many overlapping appointments each encounter has in the day.  I think I can figure out how to simply flag whether there is any overlap using ordering by attending, date and start dtm then peeking to next record to compare start and end dtms i.e.:

   If start of current < end of next and start of next >= end of current (the old "for any pair of people, if the birthdate of each is less than the date of death of the other, then they could have met"). 

It would get very complicated to use this method to count the number of times overlap occurred in any combination of attending and date.  In excel, one can use the Sumproduct function to  compare the start and end times in a row with all the rows in a range.  To make an example simple, lets remove the times and just use a hypothetical list of  start and end dates for various attendings, where "StartDt" and "EndDt" are ranges in columns B & C respectively:

Sumproduct example

How can I do the same thing in a Qlikview load script or if that is too challenging, make a chart from the raw data e.g. bar chart of number of overlaps by attending?  Failing getting the number of overlaps as a field in the table, it would be great to have a flag field to indicate whether or not each encounter has an overlap.  I'm hoping there is an easier way than peeking ahead as mentioned above - using some kind of aggregation for example.  Thanks in advance,

Bill C

[edwin]

depending on what constitute an overlap, you can aggregate it in your script and join to your fact table.

if an overlap means going to the same attending physician on the same day for each encounter, your dimensions are encounter ID (or number), attending physician, encounter date

your first aggregate by counting encounters group by encounter number, attending phy, and encounter date.  then left join to your fact table:

noconcatenate
tmpOverlaps: load EncNumber, AttPhy, EncDate, count(EncNumber) as EncCount resident Fact group by EncNumber, AttPhy, EncDate;
//this gives you either 1, 2, etc.. number of rows per encounter, attending phy, date.
inner join (Fact) load EncNumber, AttPhy, EncDate, EncCount resident tmpOverlaps;
drop table tmpOverlaps;

EncCount tells you if there are overlaps or not and how many overlaps (if =1 -> no overlap)

[BillC]

Thanks for the quick response!  Can't beat a great product and knowledgeable/helpful community!!

I realized after I greatly oversimplified the example that I didn't communicate the objective very well.  What is needed is the number of overlapping encounters by the provider for different patients - i.e. double, triple and more bookings in the day.  A good measure of health care access to a clinic is "third next available appointment" where the appointment is only single booked - for that, just a flag for overlap is sufficient to create charts.  To get a more comprehensive view, a distribution chart of non-overlapped, double, triple and even quadruple booking would be useful and would double purpose as the flag e.g. number of overlapping appointments > 0.  At the moment, I would be satisfied with just a flag, without the count, as you have done for the script above which with a bit of modification would very nicely provide a flag for a patient being seen more than once in the same day.

I'm currently heading toward using group and sorting by AttPhy, EncDate and StartTm then using previous function to first check that AttPhy and EncDate are the same and if so, determine whether or not the StartTm of current is < EndTm of previous.  Since the parsing would be sorted in ascending StartTm, it wouldn't be necessary to check the reverse - at least that the way it seems before actually trying this.  I just found a previous board post from 2017 (https://community.qlik.com/t5/QlikView-Scripting/Overlapping-intervals/td-p/1258212) that is similar to this approach substituting "production line" for physician and "product" for patient.  But I'm very eager to know if there are better ways to do this.

I appreciate your response since I'm new to Qlikview and am still trying to figure out the basics.  So basic that i've only just figured out how to import the time correctly!  There is a lot of detail in your solution that I want to take the time to understand and will likely use for repeat visits.  Hopefully I've interpreted this correctly.  Let me know if I'm totally off base.  Thanks again,

Bill C

[BillC]

Hello,

Haven't achieved the target of assigning number of overlaps for each encounter but have an overlap flag using the script logic below.  I struggled with how to deal with the first encounter of the day for a physician when using a look back or the last encounter of the day when using look ahead via descending ordering.  Either way, one encounter in each physician booked day would not be accurately processed - so tried using both in a series of resident loads and so far appears to be rock solid with various permutations/combinations of overlaps.  Note the logic that where the first pass is positive, it supersedes the second pass result since positive overlap in any direction is always right - it is the inability to look back and ahead at the same time for the first or last encounter that makes a single pass not work.  In the test table, the first pass overlap values from ascending sort are listed to show how it fails on the first encounter of the day.  OverlapFlag2 shows the net. Encounter duration is irrelevant to the calculation:

Rawdata:

LOAD Attending,EncDate,
Time(StartTm,'hh:mm') as StartTm,
Time(EndTm,'hh:mm') as EndTm

INLINE[
Attending,EncDate,StartTm,EndTm
A,2020-10-15,08:30,08:45
A,2020-10-15,08:30,09:00
A,2020-10-15,08:45,09:00
A,2020-10-15,09:00,09:15
A,2020-10-15,09:30,09:45
A,2020-10-15,09:40,10:00
A,2020-10-15,10:30,10:45
A,2020-10-15,10:45,11:00
A,2020-10-15,10:50,11:00
B,2020-11-13,09:00,09:10
B,2020-11-13,09:02,09:10
B,2020-11-13,09:10,09:40
B,2020-11-13,09:20,09:40
C,2020-09-05,13:00,13:45
C,2020-09-05,13:30,13:45
C,2020-09-05,13:45,14:00
D,2020-08-02,12:00,13:00
D,2020-08-02,13:00,13:45
D,2020-08-02,13:30,13:45];

EncTable:
Load *,
Round((EndTm-StartTm)*1440,1) as EncDuration,
If(Attending= Previous(Attending) and EncDate= Previous(EncDate)and StartTm < Previous(EndTm),1,0) as OverlapFlag

Resident Rawdata Order By Attending,EncDate,StartTm,EndTm;
Drop Table Rawdata;

EncTable2:
Load *,
If(OverlapFlag>0,OverlapFlag,If(Attending=Previous(Attending) and EncDate= Previous(EncDate)and Previous(StartTm) < EndTm,1,0)) as OverlapFlag2

Resident EncTable Order By Attending,EncDate,StartTm desc,EndTm desc;
Drop Table EncTable;

Table of script result

On to figure out how to calculate number of overlaps per encounter as well as net "facetime" with patients in a day as determined from schedule!

Bill C