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
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 Arnaldo
WOW you certainly gave me a lot of things to think about and learn. I'm a relative novice at Qlik so I had to go through your code a number of times along with the links you sent me to try get up to speed. I'm so sorry but I'm afraid I'm still running into difficulty. I can understand what you are achieving at each stage of the coding. (I didn't know anything about preceding loads before now) and I am aware that the transformations I am already doing to get the 'MorningLateStartFlag' and 'AfternoonLateStartFlag' aren't completely accurate or the correct way someone more experienced would go about it.
My full code is below....with your additional code added. I just don't know if I'm supposed to add it to the end after I've done some transformations already. I loaded my data from my qvd file first. I then had to obtain the earliest case in the morning and the earliest case in the afternoon. (Took me more than a while to figure all that out ) 😂 I should probably try to rearrange my code in order to just ascertain if the first case of morning/afternoon is late instead of all records but I can come back.
So mapping these first cases and starting fresh with OR_Stats table I then inserted your code below that. However, this might not be appropriate with regard to preceding loads? With the full code below I'm still getting this error and I'm not entirely sure why.
OR_Stats:
LOAD
PatientCaseID,
VisitID,
AppointmentID,
OperationDateTime,
OpDescription,
ScheduleOperationDateTime,
ScheduleOperationDateTime&'|'&OperationRoomID as OR_Date,
Date(Floor(ScheduleOperationDateTime), 'DD/MM/YYYY') as ScheduleOperationDate,
OperationType,
SurgeonName,
SurgeonUniqueID,
// Surgery Times
TimeFlag,
StartDateTime,
EndDateTime,
SurgeonStartDateTime,
SurgeonFinishDateTime,
TheatreStartDateTime,
TheatreEndDateTime,
StartTime,
EndTime,
Time1,
Time2,
Time3,
Time4,
Time5,
Time6,
Time7,
Time8,
Time9,
Time10,
// Late Starts/Finishes Flags
If(Time#(StartTime) > (Time#('08:15:00')), 'Late', 'On Time') as MorningLateStartFlag,
If(Time#(StartTime) > (Time#('13:45:00')), 'Late', 'On Time') as AfternoonLateStartFlag,
If(Time#(EndTime) > (Time#('13:15:00')), 'Late', 'On Time') as MorningLateFinishFlag,
If(Time#(EndTime) > (Time#('18:15:00')), 'Late', 'On Time') as AfternoonLateFinishFlag,
// Length of Surgery
Interval(SurgeonFinishDateTime-SurgeonStartDateTime, 'mm') as SurgeonLengthofSurgery,
Interval(TheatreEndDateTime-TheatreStartDateTime, 'mm') as TheatreLengthofSurgery,
OperationRoomID & '|' & Timestamp(ScheduleOperationDateTime) & '|' & TimeFlag & '|' & StartDateTime as temp_key,
OperationRoomID & '|' & Timestamp(ScheduleOperationDateTime) & '|' & TimeFlag & '|' & EndDateTime as temp_key2
FROM [lib://TQvdFilesLive/Theatre/T_OR_Stats.qvd](qvd);
NoConcatenate
FirstStartTime:
Load
OperationRoomID & '|' & Timestamp(ScheduleOperationDateTime) & '|' & TimeFlag as temporary_key,
Min(StartDateTime) as FirstStartTime
Resident OR_Stats
Group By OperationRoomID, ScheduleOperationDateTime, TimeFlag;
Map_FirstStart:
Mapping Load
temporary_key & '|' & FirstStartTime as tempkey,
'First Case' as FirstCase
Resident FirstStartTime;
LastEndTime:
Load
OperationRoomID & '|' & Timestamp(ScheduleOperationDateTime) & '|' & TimeFlag as temporary_key,
Max(EndDateTime) as LastEndTime
Resident OR_Stats
Group By OperationRoomID, ScheduleOperationDateTime, TimeFlag;
Map_LastEnd:
Mapping Load
temporary_key & '|' & LastEndTime as tempkey,
'Last Case' as LastCase
Resident LastEndTime;
Temp:
Load
*,
ApplyMap('Map_FirstStart', temp_key, 'Other Case') as FirstCase_Flag,
ApplyMap('Map_LastEnd', temp_key2, 'OtherCase') as LastCase_Flag
Resident OR_Stats;
Drop Table OR_Stats;
Rename Table Temp to OR_Stats;
Map_OR_Date_Surgeons:
Mapping Load
OR_Date,
Count(DISTINCT SurgeonUniqueID) As Surgeons
Resident OR_Stats
Group By OR_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_OR_Date_Surgeons', OR_Date, -1) As Surgeons
Resident OR_Stats
Order By "TimeStamp(StartDateTime)";
// cleansing
Drop Table OR_Stats;
Rename Table Temp_2 to OR_Stats;
