topic Rescheduled Appointments in QlikView

Rescheduled Appointments

Mon, 30 Sep 2013 19:12:05 GMT

Could you guys help me with the logic behind a question I was posed...?

I have a scheduling dashboard for a hospital that shows appointments, cancelations, provider bumps, no shows etc. I was asked to flag appointments that were canceled the same day as the appointment (less then 24 hours notice) and were rescheduled meaning someone else took that time slot.

The fields I have to work with are

Appointment Number

Appointment Timestamp

Appointment Made Timestamp

Provider#

So what I need to flag in a load script is the first Appointment Number using the Appointment Made Timestamp to book the same Appointment Timestamp and Provider#. It would have to be a flag that I could join onto my original data set

Example:

Patient Number	Appointment Timestamp	Appointment Made Timestamp	Provider #	Rescheduled Flag
1234567	9/30/2013 1:00:00PM	9/1/2013 12:00:00PM	Dr XYZ	YES
4623157	10/1/2013 1:00:00PM	9/2/2013 12:00:00PM	Dr XYZ	NO
4864321	10/2/2013 10:30:00AM	9/3/2013 12:00:00PM	Dr XYZ	NO
1863210	9/30/2013 1:00:00PM	9/28/2013 12:00:00PM	Dr XYZ	NO
1638531	10/4/2013 11:00:00AM	9/5/2013 12:00:00PM	Dr XYZ	NO
4685131	10/5/2013 1:00:00PM	9/6/2013 12:00:00PM	Dr XYZ	NO
4564132	10/6/2013 9:00:00AM	9/7/2013 12:00:00PM	Dr XYZ	NO

I have tried a couple different methods and I keep striking out... Fresh eyes would be fantastic!

Re: Rescheduled Appointments

Mon, 30 Sep 2013 19:13:20 GMT

I also have the Canceled Timestamp to figure out the Canceled with less than 24 hour notice!!

Forgot to mention that!

Re: Rescheduled Appointments

rwunderlich — Mon, 30 Sep 2013 20:03:29 GMT

Assuming your dataset is in booking order, you can assign a sequence# to each combination in the script with:

autonumber(recno(), [Appointment Timestamp] & [Provider] as AppointmentSequence

-Rob

Re: Rescheduled Appointments

Tue, 01 Oct 2013 12:31:00 GMT

Unfortunately the data is not in order of booking....

Re: Rescheduled Appointments

rwunderlich — Tue, 01 Oct 2013 14:12:37 GMT

Add an:

ORDER BY "Appointment Made Timestamp" to get them in order.

-Rob

Re: Rescheduled Appointments

Tue, 01 Oct 2013 15:57:40 GMT

Hmm... When I add the two statements it drastically impacts the load time. I even tried to take that logic out of the complexity of the dashboard and run it in its own QVW and it took 19 minutes to load 100K lines of 1.9m.

I think I may have to explore another option but thank you for your help!

Re: Rescheduled Appointments

rwunderlich — Tue, 01 Oct 2013 16:18:38 GMT

The autonumber() method can be slow when you have a lot of values, as discussed in this thread.

http://community.qlik.com/thread/48649

A faster method may be peek(), which is also shown in the thread.

-Rob

Re: Rescheduled Appointments

swuehl — Tue, 01 Oct 2013 16:22:54 GMT

Yes, I have also seen the performance impact when using autonumber with a large number of buckets / AutoIDs.

You can do something similar using peek() function on a sorted table:

Set TimeStampFormat = 'MM/DD/YYYY h:mm:ssTT';

Schedules:

LOAD * INLINE [

Patient Number, Appointment Timestamp, Appointment Made Timestamp, Provider #, Rescheduled Flag

1234567, 9/30/2013 1:00:00PM, 9/1/2013 12:00:00PM, Dr XYZ, YES

4623157, 10/1/2013 1:00:00PM, 9/2/2013 12:00:00PM, Dr XYZ, NO

4864321, 10/2/2013 10:30:00AM, 9/3/2013 12:00:00PM, Dr XYZ, NO

1863210, 9/30/2013 1:00:00PM, 9/28/2013 12:00:00PM, Dr XYZ, NO

1638531, 10/4/2013 11:00:00AM, 9/5/2013 12:00:00PM, Dr XYZ, NO

4685131, 10/5/2013 1:00:00PM, 9/6/2013 12:00:00PM, Dr XYZ, NO

4564132, 10/6/2013 9:00:00AM, 9/7/2013 12:00:00PM, Dr XYZ, NO

];

LOAD *,

if(Peek([Provider #]) = [Provider #] and Peek([Appointment Timestamp]) = [Appointment Timestamp], 'YES','NO') as RescFlag

Resident Schedules order by [Provider #], [Appointment Timestamp], [Appointment Made Timestamp] desc;

drop table Schedules;

Here, RescFlag is the created field ([Rescheduled Flag only for comparison]).

I haven't really understood your requirement regarding the 24h notice timeframe (I assumed that this should result in NO also for your first record, since cancellation and rebooking was done more than 24h prior appointment).

But if you need this, you can probably include this condition like:

LOAD *,

if(Peek([Provider #]) = [Provider #] and Peek([Appointment Timestamp]) = [Appointment Timestamp] and [Appointment Timestamp]-peek([Appointment Made Timestamp]) <1, 'YES','NO') as RescFlag

Resident Schedules

order by [Provider #], [Appointment Timestamp], [Appointment Made Timestamp] desc;

edit:

As you'll probably notice, this will not flag only the very first appointment for the appointment timestamp, but all cancelled appointments.