UPDATE AS MONTHS COMPLETE
X:\Patient Safety and Quality\Opioid Reduction Project\Processed Data\Medication data.csv
CRLF
,
False
True
28591
True
Medication data.csv
Secondary Payer
Secondary Payer = If [SecondaryPayer] in(1,2,8) then "Commercial" elseif [Second...
Warn
X:\DATA\Dx Proc Codes\ICD10 Codes.yxdb
ICD10 Codes.yxdb
X:\Patient Safety and Quality\Opioid Reduction Project\Processed Data\DX codes.csv
CRLF
,
False
True
28591
True
DX codes.csv
Simple
Value
IsNotEmpty
!IsEmpty([Value])
Simple
ICD10 Codes
IsNotEmpty
!IsEmpty([ICD10 Codes])
Warning
All
ByName
Warning
All
ByName
Data Start Date
Last
DateStr
UniqueValue
Unique Value
C:\Users\alexandra.mannerings\OneDrive - Colorado Hospital Association\Opioid project\Medication crosswalk.xlsx|||`Medication$`
False
1
Medication crosswalk.xlsx
Table=`Medication$`
C:\Users\alexandra.mannerings\OneDrive - Colorado Hospital Association\Opioid project\Final Conversion reference table.xlsx|||`Data structure$`
False
1
Final Conversion reference table.xlsx
Table=`Data structure$`
C:\Users\alexandra.mannerings\OneDrive - Colorado Hospital Association\Opioid project\route crosswalk.xlsx|||`Sheet1$`
False
1
route crosswalk.xlsx
Table=`Sheet1$`
ByName
Warning
All
ManualDelayed
Equianalgesic Dose
Equianalgesic Dose = [Admin_dose]/[Equivalency dose]
Age Group = if [AgeInYears]...
Simple
Type
!=
Drop
[Type] != "Drop"
C:\Users\alexandra.mannerings\OneDrive - Colorado Hospital Association\Opioid project\Summary Data.xlsx|||Summary
Overwrite
Summary Data.xlsx
Table=Summary
C:\Users\ALEXAN~1.MAN\AppData\Local\Temp\Engine_7848_84809ce1617a48a4bb9c92fc9a54221f_\Engine_2908_41e36ff686dd45dd937e4d4712c85b34_.yxdb
Single
Profile
X:\Patient Safety and Quality\Opioid Reduction Project\Processed Data\ED VIsit data.csv
28591
,
False
254
False
True
DoubleQuotes
1
ED VIsit data.csv
X:\Patient Safety and Quality\Opioid Reduction Project\Processed Data\Matched claims.yxdb
Matched claims.yxdb
Warning
All
ByName
C:\Users\ALEXAN~1.MAN\AppData\Local\Temp\Engine_7848_84809ce1617a48a4bb9c92fc9a54221f_\Engine_2908_216f3890b9e7444892dc2ab2f62d6dec_.yxdb
Single
Profile
C:\Users\ALEXAN~1.MAN\AppData\Local\Temp\Engine_7848_84809ce1617a48a4bb9c92fc9a54221f_\Engine_2908_41518e8f77b04d86a6fb9ca646fb34c8_.yxdb
Single
Profile
Checking dropped medications
C:\Users\ALEXAN~1.MAN\AppData\Local\Temp\Engine_7848_84809ce1617a48a4bb9c92fc9a54221f_\Engine_2908_dd364dc600a644e4879f65c7188cd87a_.yxdb
Single
Profile
Checking which medications don't match conversion
UniqueValue
Unique Value
Standardizing Route
Converting to MEU
Checking bad dates
,
C:\Users\ALEXAN~1.MAN\AppData\Local\Temp\Engine_7848_84809ce1617a48a4bb9c92fc9a54221f_\Engine_2908_566642d3b25343cba750eabc54bb88d2_.yxdb
Single
Profile
C:\Users\alexandra.mannerings\OneDrive - Colorado Hospital Association\Opioid project\Medication Combinations.xlsx|||Med Combos
Overwrite
Medication Combinations
Medication Combinations.xlsx
Table=Med Combos
Calculating Medication Combinations
Combined Medication - Ascending
Warning
All
ByName
Patient Account Number - Ascending
Medication - Ascending
C:\Users\ALEXAN~1.MAN\AppData\Local\Temp\Engine_7848_84809ce1617a48a4bb9c92fc9a54221f_\Engine_2908_c0b6c45c3b2347ffa65e1a6fa7bb7862_.yxdb
Single
Profile
C:\Users\ALEXAN~1.MAN\AppData\Local\Temp\Engine_7848_84809ce1617a48a4bb9c92fc9a54221f_\Engine_2908_08a380e0a79648529d972fedfc1f907b_.yxdb
Single
Profile
ByName
Warning
All
ManualDelayed
ByName
Warning
All
ManualDelayed
C:\Users\ALEXAN~1.MAN\AppData\Local\Temp\Engine_7848_84809ce1617a48a4bb9c92fc9a54221f_\Engine_2908_c9800574868949bfb9ca5197ae6ba8cb_.yxdb
Single
Profile
Percent Change from Baseline Average = ([Project Period]-[Avg Baseline])/[Avg Ba...
Simple
Location
=
Cohort
[Location] = "Cohort"
C:\Users\ALEXAN~1.MAN\AppData\Local\Temp\Engine_7848_84809ce1617a48a4bb9c92fc9a54221f_\Engine_2908_a7c55f9da52541f9a3b21396af0927f2_.yxdb
Single
Profile
[Year] = "2017"
Custom
[Year] = "2017"
Unique Visits Per 1,000 Visits = [Unique Visits]/[Total ED Visits]*1000
Unique M...
Custom
Warn
Warning
All
ByName
Warn
Warn
X:\Patient Safety and Quality\Opioid Reduction Project\Processed Data\cohort average summary.xlsx|||Average Summary
Overwrite
cohort average summary.xlsx
Table=Average Summary
Warning
All
ByName
Percent Change from Baseline Average = ([Avg Project]-[Avg Baseline])/[Avg Basel...
Unique Visits Per 1,000 Visits = [Unique Visits]/[Total ED Visits]*1000
Unique M...
Unique Visits Per 1,000 Visits = [Unique Visits]/[Total ED Visits]*1000
Unique M...
C:\Users\alexandra.mannerings\OneDrive - Colorado Hospital Association\Opioid project\cohort average summary.xlsx|||Summary average
Overwrite
cohort average summary.xlsx
Table=Summary average
C:\Users\ALEXAN~1.MAN\AppData\Local\Temp\Engine_7848_84809ce1617a48a4bb9c92fc9a54221f_\Engine_2908_6a8a45702536449bb6d36e02266720c5_.yxdb
Single
Profile
[Hospital ID]=524 and ([Medical Record Number] = "K000022440" OR [Patient Account Number]="N00003043372")
Custom
[Hospital ID]=524 and ([Medical Record Number] = "K000022440"...
Patient erroneously included from Boulder. Removing on request.
Location = If [Location]="Boulder Community ED" then "Boulder Foothills ED" else...
X:\Patient Safety and Quality\Opioid Reduction Project\Processed Data\Sata for stats tests.yxdb
False
True
Sata for stats tests.yxdb
Unique Visits Per 1,000 Visits = [Unique Visits]/[Total ED Visits]*1000
Unique M...
Warning
All
ByName
testing
X:\DATA\Discharge Data\2016\2016 DD ED Q1-Q4 DB_corrected.yxdb
2016 DD ED Q1-Q4 DB_corrected.yxdb
Remove
Prefix
DX
Warn
Add
Prefix
Diagnosis
X:\DATA\Discharge Data\2016\2016 DD OP-other Q1-Q4 DB_corrected.yxdb
2016 DD OP-other Q1-Q4 DB_corrected.yxdb
Remove
Prefix
DX
Warn
Add
Prefix
Diagnosis
X:\DATA\Discharge Data\2016\2016 DD OP-surgery Q1-Q4 DB_corrected.yxdb
2016 DD OP-surgery Q1-Q4 DB_corrected.yxdb
Remove
Prefix
DX
Warn
Add
Prefix
Diagnosis
X:\DATA\Discharge Data\2016\2016 DD IP Q1-Q4 Final.yxdb
2016 DD IP Q1-Q4 Final.yxdb
Remove
Prefix
DX
Warn
Add
Prefix
Diagnosis
Warning
All
ByName
[HospitalIdentificationNumber] in(302,524,596)
Custom
[HospitalIdentificationNumber] in(302,524,596)
C:\Users\alexandra.COHOSP\AppData\Local\Temp\Engine_204372_b976ea88f64d450b8d5eba31edd863f8_\Engine_204056_0f18af57f7f94812895fa7f0d9ce5e42_.yxdb
Single
Profile
Admission Date DD
X:\Patient Safety and Quality\Opioid Reduction Project\Hospital Data\EHR_596_June17.xlsx|||`June 17$`
False
1
EHR_596_June17.xlsx
Table=`June 17$`
Warning
All
ByPos
Discharge Date = If IsEmpty([Discharge Date]) then [Admin Date] else [Discharge ...
Simple
Medication
IsNotEmpty
!IsEmpty([Medication])
aka:iCHArt|||Select vwCHA1500.HospitalIdentificationNumber, vwCHA1500.Sex, vwCHA1500.ZipCode, vwCHA1500.AgeInYears, vwCHA1500.AdmissionDate, vwCHA1500.DischargeDate, vwCHA1500.PatientControlNumber, vwCHA1500.MedicalRecordNumber, vwCHA1500.PrimaryPayer, vwCHA1500.SecondaryPayer, vwCHA1500.TertiaryPayer, vwCHA1500.ObservationHours, vwCHA1500.Diagnosis16, vwCHA1500.Diagnosis17, vwCHA1500.Diagnosis18, vwCHA1500.Diagnosis19, vwCHA1500.Diagnosis20, vwCHA1500.Diagnosis21, vwCHA1500.Diagnosis22, vwCHA1500.Diagnosis23, vwCHA1500.Diagnosis24, vwCHA1500.Diagnosis25, vwCHA1500.Diagnosis26, vwCHA1500.Diagnosis27, vwCHA1500.Diagnosis28, vwCHA1500.Diagnosis29, vwCHA1500.Diagnosis30, vwCHA1500.HospitalName, vwCHA1500.PrincipalDiagnosis, vwCHA1500.Diagnosis2, vwCHA1500.Diagnosis3, vwCHA1500.Diagnosis4, vwCHA1500.Diagnosis5, vwCHA1500.Diagnosis6, vwCHA1500.Diagnosis7, vwCHA1500.Diagnosis8, vwCHA1500.Diagnosis9, vwCHA1500.Diagnosis10, vwCHA1500.Diagnosis11, vwCHA1500.Diagnosis12, vwCHA1500.Diagnosis13, vwCHA1500.Diagnosis14, vwCHA1500.Diagnosis15, vwCHA1500.TotalCharges, vwCHA1500.Race, vwCHA1500.record_id, tblRecord.submission_group From vwCHA1500 Inner Join tblRecord On tblRecord.record_id = vwCHA1500.record_id
False
True
False
Quoted
False
SQL
aka:iCHArt
Table=Select vwCHA1500.HospitalIdentificationNumber, vwCHA1500.Sex, vwCHA1500.ZipCode, vwCHA1500.AgeInYears, vwCHA1500.AdmissionDate, vwCHA1500.DischargeDate, vwCHA1500.PatientControlNumber, vwCHA1500.MedicalRecordNumber, vwCHA1500.PrimaryPayer, vwCHA1500.SecondaryPayer, vwCHA1500.TertiaryPayer, vwCHA1500.ObservationHours, vwCHA1500.Diagnosis16, vwCHA1500.Diagnosis17, vwCHA1500.Diagnosis18, vwCHA1500.Diagnosis19, vwCHA1500.Diagnosis20, vwCHA1500.Diagnosis21, vwCHA1500.Diagnosis22, vwCHA1500.Diagnosis23, vwCHA1500.Diagnosis24, vwCHA1500.Diagnosis25, vwCHA1500.Diagnosis26, vwCHA1500.Diagnosis27, vwCHA1500.Diagnosis28, vwCHA1500.Diagnosis29, vwCHA1500.Diagnosis30, vwCHA1500.HospitalName, vwCHA1500.PrincipalDiagnosis, vwCHA1500.Diagnosis2, vwCHA1500.Diagnosis3, vwCHA1500.Diagnosis4, vwCHA1500.Diagnosis5, vwCHA1500.Diagnosis6, vwCHA1500.Diagnosis7, vwCHA1500.Diagnosis8, vwCHA1500.Diagnosis9, vwCHA1500.Diagnosis10, vwCHA1500.Diagnosis11, vwCHA1500.Diagnosis12, vwCHA1500.Diagnosis13, vwCHA1500.Diagnosis14, vwCHA1500.Diagnosis15, vwCHA1500.TotalCharges, vwCHA1500.Race, vwCHA1500.record_id, tblRecord.submission_group From vwCHA1500 Inner Join tblRecord On tblRecord.record_id = vwCHA1500.record_id
Simple
HospitalIdentificationNumber
=
596
[HospitalIdentificationNumber] = 596
C:\Users\alexandra.COHOSP\AppData\Local\Temp\Engine_208872_ed67a4978a5e48eb8f891c3c464c322c_\Engine_204056_fa516417b57849209e112d2c5c563bca_.yxdb
C:\Users\alexandra.COHOSP\AppData\Local\Temp\Engine_208872_ed67a4978a5e48eb8f891c3c464c322c_\Engine_204056_cbf1f00a3b5b465c82112a3111087a47_.yxdb
C:\Users\alexandra.COHOSP\AppData\Local\Temp\Engine_208872_ed67a4978a5e48eb8f891c3c464c322c_\Engine_204056_78024fb21dbe432dac0a79d374749142_.yxdb
Single
Profile
Horizontal
4. Data processing V3