Documentation of code used.
Data cleaning and preparation
We begin by first limiting the data to only the last 3 years of surgery dates.
From there we define that if the diagnosis date is before 2007 then we use the surgery date as the diagnosis date.
From there we fix the surgery_occured field so that if there is data inserted that would indicate a surgery occurred then we still class that as a surgery being performed. (There is a surgery date entered and there was a procedure type entered)
Finally we define a few fields that are then used in the report.
Determines if a surgery occurred by seeing if a surgery happened or if there was a procedure date and potentially not classed as a surgery.
We then define the anastomotic rate by using the data to determine if there was a leak after an anastomosis was formed.
We also update the discharge date with the surgery if no discharge date is provided in the dataset.
We then create a few fields that help with calculating certain metrics in the report.
Risk Adjustment Process
To begin a separate analysis is conducted to determine which fields are significant influencers on a particular metric.
Once we have identified those fields they perform a regression with the target variable being the metric (ie Inpatient Death) and the descriptor variable being the identified variables in the previous step. The outcome of the regression is to see how much each variable influences that metric.
Then we produce a predicted score amount for each hospital based on the number of surgeries they have performed.
Then once we have calculated the predicted amount, we then apply 1 of three formulas depending what type of metric it is. The formula is chosen depending on what type of metric we are looking at.
These formulas check if you (a hospital or a clinician) are higher or lower than the predicted amount as a ratio. It then takes the overall population average of the population at that point (number of surgeries or instances) and then multiplies that value by the ratio calculated then rounds this final value up to the nearest point.
That final rounded value is the risk adjusted amount for that hospital or clinician is the risk adjusted value that has been calculated for them. This process has then been done for each of the metrics in the annual report.
Raw code:
R script:
Qlik load script:
[BCCA_extract_V1]:
LOAD Distinct //**** Creates fields like years_in_practice,
//****length of stay after fields have been cleaned,
//****login_id to validate if we identify users based on their access,
//****patient age at diagnosis
//****also creates the logic to determine for permanent stoma rate that then gets used in the front end
*,
Year(Today())-year_of_graduation as years_in_practice,
[discharge_date]-[admission_date] as length_of_stay,
//ApplyMap('QlikUserIDMAP',login_name,Null()) as login_id,
YEAR(diagnosis_date) as diagnosis_year,
YEAR(surgery_date) as surgery_year,
Round((diagnosis_date-date_of_birth)/365.25,0) as patient_age_at_diagnosis,
Round((surgery_date-date_of_birth)/365.25,0) as patient_age_at_surgery,
IF( surgery_occured=1 and
colon_or_rectal_cancer='Rectal' and
(procedure_type_code=11 or procedure_type_code=12)
,1,0) as permanent_stoma_end_denominator,
IF( surgery_occured=1 and
colon_or_rectal_cancer='Rectal' and
(procedure_type_code=11 or procedure_type_code=12) and
(stoma_type_code=2 or stoma_type_code = 4)
,1,0) as permanent_stoma_end_numerator
;
LOAD //****TO Load in the mapping for the year of graduation
*,
ApplyMap('GradDatesMap',login_name,Null()) as year_of_graduation,
IF(//isnull([discharge_date_original]) or
YEAR([discharge_date_original])=1900
,Date(surgery_date),Date([discharge_date_original])) AS [discharge_date]
;
LOAD //**** This part loads needed fields from the extract. It also maps the coded fields to the uncoded values
//**** It creates the following,
//**** consultant number so consultants can not be identified but still allows
//**** Updates diagnosis date if it is before 2007 then we use the surgery date as the diagnosis date
//**** Determines if a surgery occurred by seeing if a surgery happened or if there was a procedure date and potentially not classed as a surgery.
//**** We then define the anastomotic rate by using the data to determine if there was a leak after an anastomosis was formed.
[row],
[Treatment_Episode_id],
[Patient_id],
[sex],
ApplyMap('GenderMap',sex) as gender,
[date_of_birth],
// [last_name],
// [first_name],
// [first_name] & ' ' & [last_name] as consultant_name,
//****Original ****LOWER(first_name) & '.' & LOWER([last_name]) as login_id,
//Hash128(IF(LOWER(first_name) & '.' & LOWER([last_name]) = 'philip.smart','naunghton.williams',LOWER(first_name) & '.' & LOWER([last_name]))) as login_name,
// LOWER(first_name) & '.' & LOWER([last_name]) as login_name,
AutoNumber(consultant_id) AS consultant_number,
[consultant_id],
[name],
//[name] as [hospital_name],
[hospital_id],
Date(IF([diagnosis_date]= null() or YEAR([diagnosis_date])<= Year(2007),surgery_date,[diagnosis_date])) AS [diagnosis_date],
//[public_private] as Public_or_Private_code,
ApplyMap('PublicvPrivateHospitalMap',[public_private]) as Public_or_Private,
[screen_detected],
[tumour_diag_scr_fobt],
[nat_bowel_scr_prog],
If(nat_bowel_scr_prog=1,'Screened','Non-screened') as screened_patient,
[rectal_cancer],
[dbo_tbl_Treatment_Episode.rectal_cancer_height],
[rectal_cancer_preop_staging],
[rectal_staging_ultrasound],
[rectal_staging_mri],
[rectal_staging_clinical_ct],
[rectal_staging_unknown],
//[preop_t_staging] as Preop_t_staging_code,
ApplyMap('Preop_t_stagingMap',[preop_t_staging]) as Preop_t_staging,
//[preop_n_staging] as Preop_n_staging_code,
ApplyMap('Preop_n_stagingMap',[preop_n_staging]) as Preop_n_staging,
[neoadjuvant_therapy],
[neoadjuvant_therapy_type] as neoadjuvent_therapy_type_code,
//ApplyMap('neoadjuvent_therapy_typeMap',[neoadjuvent_therapy_type]) as neoadjuvent_therapy_type,
//[discussed_at_mdm] as discussed_at_mdm_code,
ApplyMap('discussed_at_mdmMap',[discussed_at_mdm]) as discussed_at_mdm,
[stent],
//[stent_indication] as stent_indication_code,
ApplyMap('stent_indicationMap',[stent_indication]) as stent_indication,
[stent_decompression],
[stent_perforation],
If([surgery_planned] <> 1, 0,[surgery_planned]) as surgery_planned,
//[reason_for_no_surgery] as reason_for_no_surgery_code,
ApplyMap('reason_for_no_surgeryMap',[reason_for_no_surgery]) as reason_for_no_surgery,
[reason_for_no_surgery_other],
[surgery_date],
If([surgery_planned] <> 1,
IF(not(isnull(surgery_date)) and not(isnull(procedure_type)),1,0)
,[surgery_planned]) as surgery_occured,
IF(MONTH(surgery_date)=7 or MONTH(surgery_date)=8 or MONTH(surgery_date)=9,'Q1' & ' ' & TEXT(YEAR(surgery_date)),
IF(MONTH(surgery_date)=10 or MONTH(surgery_date)=11 or MONTH(surgery_date)=12,'Q2' & ' ' & TEXT(YEAR(surgery_date)),
IF(MONTH(surgery_date)=1 or MONTH(surgery_date)=2 or MONTH(surgery_date)=3,'Q3' & ' ' & TEXT(YEAR(surgery_date)-1),
IF(MONTH(surgery_date)=4 or MONTH(surgery_date)=5 or MONTH(surgery_date)=6,'Q4' & ' ' & TEXT(YEAR(surgery_date)-1),
'No surgery date'
)))) as surgery_quarter, //Creates surgery quarter based on date
Month(surgery_date)& ' '& year(surgery_date) as surgery_month_year,
[Age],
//[operative_urgency] as operative_urgency_code,
ApplyMap('operative_urgencyMap',[operative_urgency]) as operative_urgency,
//[most_senior_person] as most_senior_person_code,
ApplyMap('most_senior_personMap',[most_senior_person]) as most_senior_person,
[asa_score],
//[episode] as episode_code,
ApplyMap('episodeMap',[episode]) as episode,
[subsequent_operation],
//[surgical_entry] as surgical_entry_code,
IF(surgical_entry = 1,'Open','Minimally invasive') as invasive_open,
ApplyMap('Surgical_entryMap',[surgical_entry]) as surgical_entry,
[number_of_tumours],
[dbo_tbl_Operative.rectal_cancer_height],
[height],
[weight],
[bmi_manual],
[procedure_type] as procedure_type_code,
ApplyMap('procedure_typeMap',[procedure_type]) as procedure_type,
[procedure_type_other],
//[reconstruction_method] as reconstruction_method_code,
ApplyMap('reconstruction_methodMap',[reconstruction_method]) as reconstruction_method,
[anastomosis_formed],
IF(surgical_complications = 0 or anastomosis_formed = 0,0,anastomotic_leak)
as anastomotic_leak_corrected,
[anastomosis_height],
//[anastomosis_technique] as anastomosis_technique_code,
ApplyMap('anastomosis_techniqueMap',[anastomosis_technique]) as anastomosis_technique,
//[anastomosis_oversewn] AS anastomosis_oversewn_code,
ApplyMap('anastomosis_oversewnMap',[anastomosis_oversewn]) as anastomosis_oversewn,
[cancer_resected],
//[stoma_formed] as stoma_formed_code,
ApplyMap('stoma_formedMap',[stoma_formed]) as stoma_formed,
IF(IsNull(procedure_type),null(),
IF(procedure_type=2 or
procedure_type=4 or
procedure_type=11 or
procedure_type=12,1,0)) as end_stoma,
[stoma_type] as stoma_type_code,
ApplyMap('stoma_typeMap',[stoma_type]) as stoma_type,
//[curability] as curability_code,
ApplyMap('curabilityMap',[curability]) as curability,
[organs_resected],
[resected_organs],
IF(ISNULL([admission_date]) or YEAR([admission_date])=1900, surgery_date,[admission_date])
AS [admission_date],//Attempting to fix the the date fields for more accurate length of stay
[admission_date] as [admission_date_original],
Date([discharge_date]) as [discharge_date_original],
[surgical_complications],
[abdominal_pelvic_collection],
[anastomotic_leak],
//[anastomotic_leak_diagnosis] as anastomotic_leak_diagnosis_code,
ApplyMap('anastomotic_leak_diagnosisMap',[anastomotic_leak_diagnosis]) as anastomotic_leak_diagnosis,
[anastomotic_leak_surgery],
//[anastomotic_leak_correction] as anastomotic_leak_correction_code,
ApplyMap('anastomotic_leak_correctionMap',[anastomotic_leak_correction]) as anastomotic_leak_correction,
if(/*surgical_complications=1 and*/ anastomosis_formed = 1,1,0) as anastomotic_leak_rate_denominator,
if((/*surgical_complications=1 and*/ anastomosis_formed = 1) and anastomotic_leak = 1,1,0) as anastomotic_leak_rate_numerator,
[entercutaneous_fistula],
[superficial_wound_dehiscence],
[deep_wound_dehiscence],
[wound_infection],
[wound_infection_abdominal],
[wound_infection_perineal],
[wound_infection_drain_tube_site],
[sepsis],
//[sepsis_cause] as sepsis_cause_code,
ApplyMap('sepsis_causeMap',[sepsis_cause]) as sepsis_cause,
//[sepsis_organism_grown] as sepsis_organism_grown_code,
ApplyMap('sepsis_organism_grownMap',[sepsis_organism_grown]) as sepsis_organism_grown,
[prolonged_ileus],
[small_bowel_obstruction],
[urinary_retention],
[ureteric_injury],
[splenectomy],
[postoperative_haemorrhage],
[other_surgical_complications],
[other_surgical_complications_desc],
[medical_complications],
[dvt_pe],
[chest_infection],
[cardiac],
[other_medical_complications],
[other_medical_complications_desc],
[returned_to_theatre],
Date([returned_to_theatre_date] ) AS [returned_to_theatre_date],
IF(surgery_date = null(),1,
IF(returned_to_theatre_date-surgery_date<=30,1,0))
as [returned_to_theatre_in_30_days],
[returned_to_theatre_reason],
//[readmitted_30_days] as readmitted_30_days_code,
ApplyMap('readmitted_30_daysMap',[readmitted_30_days]) as readmitted_30_days,
[inpatient_death],
[inpatient_death_date],
//[inpatient_death_cause] as inpatient_death_cause_code,
ApplyMap('inpatient_death_causeMap',[inpatient_death_cause]) as inpatient_death_cause,
[mortality_30_day],
[tumour_site] as tumour_site_code,
ApplyMap('tumour_siteMap',[tumour_site]) as tumour_site,
IF(tumour_site>1 and tumour_site<10,1,0) as colon_cancer_flag,
IF(tumour_site>1 and tumour_site<10,'Colon',
IF(tumour_site>9 and tumour_site<13,'Rectal','Other')) as colon_or_rectal_cancer,
[tumour_type],
[primary_tumour],
//[t_stage] as t_stage_code,
ApplyMap('t_stageMap',[t_stage]) as t_stage,
//[n_stage] as n_stage_code,
ApplyMap('n_stageMap',[n_stage]) as n_stage,
//[m_stage] as m_stage_code,
ApplyMap('m_stageMap',[m_stage]) as m_stage,
//[overall_stage] as overall_stage_code,
ApplyMap('overall_stageMap',[overall_stage]) as overall_stage,
[dbo_tbl_Tumour.mets_site_abdo_pelvic_lymph],
[dbo_tbl_Tumour.mets_site_bone],
[dbo_tbl_Tumour.mets_site_brain],
[dbo_tbl_Tumour.mets_site_distant_node],
[dbo_tbl_Tumour.mets_site_liver],
[dbo_tbl_Tumour.mets_site_lung],
[dbo_tbl_Tumour.mets_site_omentum],
[dbo_tbl_Tumour.mets_site_ovarian],
[dbo_tbl_Tumour.mets_site_peritoneal],
[dbo_tbl_Tumour.mets_site_other_intra_abdo],
[dbo_tbl_Tumour.mets_site_other],
[dbo_tbl_Tumour.mets_site_other_desc],
[lymph_nodes_harvested],
IF(lymph_nodes_harvested = null(), 0,1)
as was_lymph_nodes_harvested,
[positive_nodes],
[lymphovascular_invasion],
//[circumferential_margins] as circumferential_margins_code,
ApplyMap('circumferential_marginsMap',[circumferential_margins]) as circumferential_margins,
[mucosal_margins_result],
[mucosal_margins],
[mucosal_margins_clear],
//[tumour_regression_grade] as tumour_regression_grade_code,
ApplyMap('tumour_regression_gradeMap',[tumour_regression_grade]) as tumour_regression_grade,
[offered],
[not_offered_early_stage],
[not_offered_patient_age],
[not_offered_late_stage],
[not_offered_comorbidity],
[not_offered_metastatic_disease],
[not_offered_recurrent_disease],
[not_offered_patient_declined],
[not_offered_other],
[not_offered_other_desc],
[chemotherapy],
[radiotherapy],
[followup_date],
//[patient_status] as patient_status_code,
ApplyMap('patient_statusMap',[patient_status]) as patient_status,
[death_date],
//[death_cause] as death_cause_code,
ApplyMap('death_causeMap',[death_cause]) as death_cause,
[last_followup],
[last_followup_reason],
//[lost_followup_reason] as lost_followup_reason_code,
ApplyMap('lost_followup_reasonMap',[lost_followup_reason]) as lost_followup_reason
FROM [lib://BCCA Report:DataFiles/BCCA_extract_v1.xlsx]
(ooxml, embedded labels, table is BCCA_extract_V1)
//Old AGINIC INTERNAL LOAD
// FROM [lib://AttachedFiles/BCCA_extract_v1.xlsx]
// (ooxml, embedded labels, table is BCCA_extract_V1)
WHERE Year([surgery_date])>=Year(Today())-3 or NOT(IsNull(surgery_date))
//*****Filters the data to last 3 years only
;