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: 
Not applicable

Rescheduled Appointments

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 NumberAppointment TimestampAppointment Made TimestampProvider #Rescheduled Flag
12345679/30/2013 1:00:00PM9/1/2013 12:00:00PMDr XYZYES
462315710/1/2013 1:00:00PM9/2/2013 12:00:00PMDr XYZNO
486432110/2/2013 10:30:00AM9/3/2013 12:00:00PMDr XYZNO
18632109/30/2013 1:00:00PM9/28/2013 12:00:00PMDr XYZNO
163853110/4/2013 11:00:00AM9/5/2013 12:00:00PMDr XYZNO
468513110/5/2013 1:00:00PM9/6/2013 12:00:00PMDr XYZNO
456413210/6/2013 9:00:00AM9/7/2013 12:00:00PMDr XYZNO

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

7 Replies
Not applicable
Author

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

Forgot to mention that!

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Not applicable
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Add an:

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


-Rob

Not applicable
Author

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!

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

swuehl
MVP
MVP

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.