| Version 10 (modified by , 12 years ago) ( diff ) |
|---|
The PATS mappings are split into text and numeric fields.
Text Fields
Text fields are mapped to the i2b2 Observation Fact table like this
| i2b2 Field | Source |
| Encounter_Num | i2b2 Patient ID |
| Patient_Num | i2b2 PatientID |
| Concept_Cd | Enum lookup value (see below) |
| Provider_Id | "@" |
| Start_Date | The source table observation date field (see below) |
| Modifier_Cd | "@" |
| ValType_Cd | "T" (text) |
| Units_Cd | "@" |
| Location_Cd | "@" |
| Update_Date | The current date using the GETDATE() Function |
| SourceSystem_Cd | "BRICCS" |
| Upload_Id | "1" |
SOMETIME (RB): Convert Update_Date to use a date parameter. It's just a little quicker.
SOMETIME (RB): Should the SourceSystem_Cd not be "PATS"? Or maybe "MINAP" or whatever.
SOMETIME (RB): The Upload_Id is presumably always one because everything is reloaded each time. Might be nice to change this to something more meaningful.
Enum Lookup Value
The Concept Code to use is found in the table DATA_DICTIONARY_ENUM_VALUES in the DWBRICCS database on UHLDWH server. This table contains records for concept code keyed on source table name, column name and column value. NULL column values may be mapped to the field '(empty)'.
ACTION (RB): Check if all possible values of column value contain a mapping in the DATA_DICTIONARY_ENUM_VALUES table.
Source Table Observation Date Field
For each source table in the there is a single field that is used as the start date for all columns from that field. The observation date field to use for each table is stored in the pats_tables table in the DWBRICS on the UHLDWH server.
Numeric Fields
Numeric fields are mapped to the i2b2 Observation Fact table like this
| i2b2 Field | Source |
| Encounter_Num | i2b2 Patient ID |
| Patient_Num | i2b2 PatientID |
| Concept_Cd | "PTS:{TABLE_NAME}_{COLUMN_ID}" |
| Provider_Id | "@" |
| Start_Date | The source table observation date field (see above) |
| Modifier_Cd | "@" |
| ValType_Cd | "N" (numeric) |
| TVal_Char | "E" (see below) |
| Units_Cd | Value calculated by ufn_get_units function (see below) |
| Location_Cd | "@" |
| Update_Date | The current date using the GETDATE() Function |
| SourceSystem_Cd | "BRICCS" |
| Upload_Id | "1" |
TVal_Char
The TVal_Char describes the meaning of the value contained in the NVal_Num field.
For all values currently the value of TVal_Char is set to "E", which means equals. That is, the value in the NVal_Num field is the result of the test. All possible values of TVal_Char are:
| Value | Meaning |
| E | Equals |
| NE | Not Equal |
| L | Less Than |
| LE | Less Than or Equals |
| G | Greater Than |
| GE | Greater Than or Equals |
From a casual look at the data, I think that a value of less than X, or greater than Y is sometimes used for Pathology data.
ufn_get_units Function
The function ufn_get_units in the DWBRICCS database is called with parameters of table_name and field_name to determine the units.
ACTION (RB): Work out what this function actually does and then move the logic into a join if possible. This should be clearer and also quicker.
Tables
CATH_CATHINITIALVW
| Field Name | Column ID | Type | Comments |
| EntryId | 1 | int | |
| PatientId | 2 | int | |
| DateofCatheter | 3 | date | |
| TimeofCatheter | 4 | time | |
| EntryStatus | 5 | varchar | |
| PreviousEntry | 6 | int | |
| NextEntry | 7 | int | |
| DateOfAdmission | 8 | date | |
| AgeAtCatheter | 9 | int | |
| DoneInMobileLab | 10 | varchar | |
| Consultant | 11 | varchar | |
| Operator | 12 | varchar | |
| SecondOperator | 13 | varchar | |
| Technician | 14 | varchar | |
| PatientType | 15 | varchar | |
| WasThePatientCatheterised | 16 | varchar | |
| ProcedureType | 17 | varchar | |
| GeneralAnaesthetics | 18 | varchar | |
| ProcedureSite | 19 | varchar | |
| BiopsyTaken | 20 | varchar | |
| BiopsySite | 21 | varchar | |
| OtherProcedure | 22 | varchar | |
| Arterial_1 | 23 | varchar | |
| Vessel_1_Arterial | 24 | varchar | |
| Complicated_1_Arterial | 25 | varchar | |
| Arterial_2 | 26 | varchar | |
| Vessel_2_Arterial | 27 | varchar | |
| Complicated2Arterial | 28 | varchar | |
| Arterial_3 | 29 | varchar | |
| Vessel_3_Arterial | 30 | varchar | |
| Complicated_3_Arterial | 31 | varchar | |
| Venous_1 | 32 | varchar | |
| Vessel_1_Venous | 33 | varchar | |
| Complicated_1_Venous | 34 | varchar | |
| Venous_2 | 35 | varchar | |
| Vessel_2_Venous | 36 | varchar | |
| Complicated_2_Venous | 37 | varchar | |
| Venous_3 | 38 | varchar | |
| Vessel_3_Venous | 39 | varchar | |
| Complicated_3_Venous | 40 | varchar | |
| Vessels | 41 | varchar | |
| ReturnedTo | 42 | varchar | |
| FurtherProcedure | 43 | varchar | |
| ImmediateComplications | 44 | varchar | |
| ProcedureAbandoned | 45 | varchar | |
| ReasonForAbandoningProcedure | 46 | varchar | |
| OtherComplications | 47 | varchar | |
| DiedInCathLab | 48 | varchar | |
| CoronaryDisease | 49 | varchar | |
| SignificantValveDisease | 50 | varchar | |
| SignificantLvDisease | 51 | varchar | |
| Outcome | 52 | varchar | |
| ReturnToCathLab | 53 | varchar | |
| EmbolicSequelae | 54 | varchar | |
| NonEmergencySurgery | 55 | varchar | |
| GroinComplication | 56 | varchar | |
| Death | 57 | varchar | |
| DischargeComplications | 58 | varchar | |
| OutcomeOfCatheter | 59 | varchar | |
| DateOfDischarge | 60 | date | |
| DateOfDeath | 61 | date | |
| DischargeTo | 62 | varchar | |
| PreopStay_days | 63 | int | |
| PostOpStay_days | 64 | int | |
| TotalHospitalStay | 65 | int | |
| IhdScore_cardwl | 66 | int | |
| NihdScore_cardwl | 67 | int | |
| OtherComplicationsPreDischarge | 68 | varchar | |
| DateOfDeathPostDischarge | 69 | date | |
| PostProcedureDeathInterval_days | 70 | int | |
| FormsVerified | 71 | varchar | |
| Contract | 72 | varchar | |
| Pcg | 73 | varchar | |
| AuditLoopClosed | 74 | varchar | |
| DateAuditLoopClosed | 75 | date | |
| ReasonAuditLoopClosed | 76 | varchar | |
| IntervalBetweenProc_LoopClosed_days | 77 | int | |
| DateOnWaitingList | 78 | date | |
| DaysOnList | 79 | int | |
| GpPracticeCode | 80 | varchar | |
| Pct | 81 | varchar | |
| MedicalRecordNumber | 82 | varchar | |
| AdministrativeCategory | 83 | varchar | |
| AuditTrail | 84 | text | |
| DateAndTimeOfProcedure | 85 | varchar | |
| IsThisAcs | 86 | varchar | |
| Sub_Xiphoid | 87 | varchar | |
| PreDischargeComplications | 88 | varchar | |
| IfOtherPreDischargeComplicationsPleaseState | 89 | varchar | |
| Height | 90 | float | |
| Weight | 91 | float | |
| Bmi | 92 | float | |
| ScreeningTime | 93 | float | |
| ScreeningDose | 94 | float | |
| ExceededDrl | 95 | varchar | |
| Radiographer | 96 | varchar | |
| ReasonForExceedingDrl | 97 | varchar | |
| CatheterProcedureReport | 98 | varchar | |
| ConsultantCode | 99 | varchar | |
| OperatorCode | 100 | varchar | |
| SecondOperatorCode | 101 | varchar | |
| TroponinPositive | 102 | varchar | |
| DateOfCurrentStatus | 103 | date | |
| DateOfEndStatus | 104 | date | |
| DateTimeRecordCreated | 105 | varchar | |
| DateTimeRecordLastChanged | 106 | varchar | |
| EnteredByTag | 107 | varchar | |
| EntryCurrentStatus | 108 | varchar | |
| EntryEndStatus | 109 | varchar | |
| HospitalTag | 110 | varchar | |
| ImportLinkId | 111 | int |
