Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
I also have the Canceled Timestamp to figure out the Canceled with less than 24 hour notice!!
Forgot to mention that!
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
Unfortunately the data is not in order of booking....
Add an:
ORDER BY "Appointment Made Timestamp" to get them in order.
-Rob
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!
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
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.