Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I’m trying to count late starts per OR per day in the morning and afternoon sessions. I’ve been able to highlight if the operation took place in the morning session or the afternoon session by looking at the ‘start time’ of the operation (if it is <13:00 then its morning otherwise its afternoon). However this isn’t always accurate. (explained below). I’ve also ascertained if it was the first case of the morning or afternoon session by getting the earliest start time of each session.
Where I am having difficulty is:
- Excluding late afternoon sessions if the surgeon is the same for the whole day (figure 1) – this is the most important
- I need to recalculate the ‘First Case’ of the afternoon based on the surgeon change first and then based on the earliest time – this is secondary but would still love some input
Here is an example of my data.
Figure 1: List for OR01 on 27/02/2015 with only 1 surgeon for the whole day. I want to exclude the first case late start flag. I’ve been trying to pull the surgeon from the first case of the day and the last case of the day and if they are equal then exclude
Below for 09/02/2015 in OR02 there were 2 surgeons for the day.
- Morning session – first case on time
- Afternoon session – first case on time however it was the same surgeon for the morning session
- The actual first session of the afternoon should have been the first case for surgeon SN61 and not surgeon NN36 last case
Any help is appreciated.
Thanks
Ciara
Accepted Solutions
Hi @Ciara
Thank you for the RawData, it is a lot easier to work with real data instead of air-code 🙂; I focus this reply on the 27/02/2015 records, then we could focus on the 09/02/2015 because my questions as data integrity between your report and the application's report is still pending 😯; before writing my reply, below is the list of Qlik features and Qlik Community questions that help writing the script (in no particular order):
I implemented several levels of preceding loads in the script, basically to comply with the Separation of Concerns design principle, you may implement one or two levels of SoC in your script, if you chose to use what is written in this reply; here is the script:
NoConcatenate
Temp_1:
LOAD
ScheduleOperationDate,
OperationRoomID,
SurgeonUniqueID,
TimeFlag,
FirstCase_Flag,
"TimeStamp(StartDateTime)",
"TimeStamp(EndDateTime)",
MorningLateStartFlag,
AfternoonLateStartFlag,
ScheduleOperationDate & '|' & OperationRoomID As OM_Date
FROM [lib://Surgeons/RawData.xlsx]
(ooxml, embedded labels, table is RawData);
Map_OM_Date_Surgeons:
Mapping Load
OM_Date,
Count(DISTINCT SurgeonUniqueID) As Surgeons
Resident Temp_1
Group By OM_Date;
// transformations
NoConcatenate
// - applying rule on AfternoonLateStartFlag on the SurgeonId + TimeFlag group
Temp_2:
Load *,
If(Surgeons > 1, AfternoonLateStartFlag, if(Surgeons = 1 and AfternoonLateStartFlag = 'Late' and TimeFlag_RT = 1, 'Exclude Late', AfternoonLateStartFlag)) As T2_AfternoonLateStartFlag
;
// - adding group running total on SurgeonId + TimeFlag
Load *,
If(RecordNo = 1, 1, if(TimeFlag = Peek('TimeFlag') and SurgeonUniqueID = Peek('SurgeonUniqueID'), Peek('TimeFlag_RT') + 1, 1 )) As TimeFlag_RT
;
// - ecluding ALL the late in the AfternoonLateStartFlag
Load *,
If(Surgeons > 1, AfternoonLateStartFlag, If(Surgeons = 1 and AfternoonLateStartFlag = 'Late', 'Exclude Late', AfternoonLateStartFlag)) As T_AfternoonLateStartFlag,
RecNo() as RecordNo;
Load *,
ApplyMap('Map_OM_Date_Surgeons', OM_Date, -1) As Surgeons
Resident Temp_1
Order By "TimeStamp(StartDateTime)";
// cleansing
Drop Table Temp_1;
Rename Table Temp_2 to RawData;
Comments:
The Map table on OR_Date was created immediately after loading the RawData into the Temp_1 table; We need the DISTINCT modifier of the COUNT function.
You read preceding loads bottom to top
The first thing done when processing the RawData in the Temp_1 table was perform the ApplyMap with the Count of Surgeons, the column was named Surgeons.
The first preceding load count the records saving the result into the column RecordNo; then it applies the rule on the 'Late' AfternoonLateStartFlag without mercy, It creates a new column for the result, named T_AfternoonLateStartFlag; you may not do that in your solution, but it is a good practice for future debugging.
The merciless transformation to create the T_AfternoonLateStartFlag, does not return the result you specified in your question; We added a new group running count, this one reset to 1 when you get a new SurgeonId + TimeFlag group. This will help us to transform the first time the surgeon was late on the first afternoon TimeFlag, if any; this is very generic in my opinion, what about the surgeon started at 06:00 PM on the afternoon; anyhow, this group running count was named TimeFlag_RT.
The next preceding load applies the rule to reset the first 'Late' on the AfternoonLateStartFlag when required, the column was named T2_AfternoonLateStartFlag; once again a new column was introduced for the same reason given above.
We finish the load script with its cleaning, dropping the table Temp_1 and renaming Temp_2 to RawData.
These are the solution's screenshots:
The above screenshot show the number of surgeons per Day-OR, the Count(Distinct worked.
The merciless transformation done on the AfternoonLateStartFlag was not good enough!
This screenshot shows the running counts, both of them, and the highlighted data confirm the group running count reset exactly where it shoud.
Finally, this screenshot compares the AfternoonLateStartFlag transformations side by side.
Best regards,
Arnaldo Sandoval
Hi Ciara,
I will suggest you to add a column to the table counting the number of Surgeons per Day per OR, something like this; I named the column: SurgeonCount ; it will be good if you can add this column while loading your application-dashboard data, this column will simplify your logic described here:
| I’ve been trying to pull the surgeon from the first case of the day and the last case of the day and if they are equal then exclude |
Because when the SurgeonCount is equal to 1, you can exclude the data with a cleaner and probably efficient way.
Now, regarding your second scenario, with two surgeons sharing the OR on the same day (09.Feb.2015), you wrote this:
|
This rule confused me, I have difficulties understanding why the surgeon performing the first surgery of the afternoon suddenly hands it over to the second surgeon. My confusion is because this rule make no sense at all, your report will produce misleading results, other reports in the organization will not reconcile with your report; Would you please elaborate justifying the rule you wrote?
Hope this helps,
Arnaldo Sandoval
