| Destination Field | Source field | Logic | Comment field |
|---|---|---|---|
| observation_id | A unique key given to an Observation record for a Person. Each instance of an observation present in the source data should be assigned this unique key. This is to be an auto generated number (integer) for every recorded inserted. Data type: bigint Required: yes Primary key: yes Foreign key: no Foreign key table: NA Foreign key domain: NA | ||
| person_id | The PERSON_ID of the Person for whom the Observation is recorded. Data type: bigint Required: yes Primary key: no Foreign key: yes Foreign key table: PERSON Foreign key domain: NA Important note for ETL: This is a foreign key referencing to the person_id in the PERSON table. | ||
| observation_concept_id | The CONCEPT_ID that the OBSERVATION_SOURCE_CONCEPT_ID maps to. There is no specified domain that the Concepts in this table must adhere to. The only rule is that records with Concepts in the Condition, Procedure, Drug, Measurement, or Device domains MUST go to the corresponding table. Specimen received in laboratory from the Observation Domain = 45768534 Data type: integer Required: yes Primary key: no Foreign key: yes Foreign key table: CONCEPT Important note for ETL: If no code is found, set it to 0. | ||
| observation_date | d_lab_sent_resu_dist | Store the d_lab_sent_resu_dist, the date on which the lab has sent the result to district, to populate this field. | Store it in YYYY-MM-DD format. The date of the Observation. Depending on what the Observation represents this could be the date of a lab test, the date of a survey, or the date a patient’s family history was taken. Data type: date Required: no Primary key: no Foreign key: no Foreign key table: NA |
| observation_datetime | d_lab_sent_resu_dist | Store the d_lab_sent_resu_dist, the date on which the lab has sent the result to district, to populate this field. | Store it in YYYY-MM-DD HH:MM:ss format. Since no time is specified in the source data so set it to midnight (00:00:00). The date of the Observation. Depending on what the Observation represents this could be the date of a lab test, the date of a survey, or the date a patient’s family history was taken. If no time is given set to midnight (00:00:00). Data type: datetime (datetime without timezone) Required: no Primary key: no Foreign key: no Foreign key table: NA |
| observation_type_concept_id | The CONCEPT_ID that the OBSERVATION_SOURCE_CONCEPT_ID maps to. There is no specified domain that the Concepts in this table must adhere to. Observation type concept id: 32809 for case report form Data type: integer Required: yes Primary key: no Foreign key: yes Foreign key table: CONCEPT Foreign key domain: Type concept Important note for ETL: If no code is found, set it to 0. | ||
| value_as_number | This is the numerical value of the Result of the Observation. It is not expected that all Observations will have numeric results, rather, this field is here to house values should they exist. Set it to blank (NULL). Data type: float Required: no Primary key: no Foreign key: no Foreign key table: NA Foreign key domain: NA | ||
| value_as_string | This is the categorical value of the Result of the Observation, if applicable and available. Set it to blank (NULL). Data type: varchar(60) Required: no Primary key: no Foreign key: no Foreign key table: NA Foreign key domain: NA | ||
| value_as_concept_id | The value of VALUE_AS_CONCEPT_ID may be provided through mapping from a source Concept which contains the content of the Observation. Specimen received - from the Observation domain = 4234913 No sample received - from the Observation domain = 4104933 Here since the date is mentioned that the specimen has been received, so the value_as_concept_id will be specimen received (4234913). Data type: integer Required: no Primary key: no Foreign key: yes Foreign key table: CONCEPT Foreign key domain: NA | ||
| qualifier_concept_id | This field contains all attributes specifying the clinical fact further, such as as degrees, severities, drug-drug interaction alerts etc. Here we are using it to specify the adequacy of the specimen collected. Data type: integer Required: no Primary key: no Foreign key: yes Foreign key table: CONCEPT Foreign key domain: NA | ||
| unit_concept_id | There is no standardization requirement for units associated with OBSERVATION_CONCEPT_IDs Set it to blank (NULL). Data type: integer Required: no Primary key: no Foreign key: yes Foreign key table: CONCEPT Foreign key domain: NA | ||
| provider_id | This is a foreign key referencing to the provider_id in the PROVIDER table. Data type: bigint Required: no Primary key: no Foreign key: yes Foreign key table: Provider Foreign key domain: NA | ||
| visit_occurrence_id | The visit during which the condition occurred or has been reported. This is a foreign key referencing to the visit_occurrence_id in the VISIT_OCCURRENCE table. Data type: bigint Required: no Primary key: no Foreign key: Yes Foreign key table: VISIT_OCCURRENCE Foreign key domain: NA | ||
| visit_detail_id | The VISIT_DETAIL record during which the condition occurred or has been reported. This is a foreign key referencing to the visit_detail_id in the VISIT_DETAIL table. Data type: bigint Required: no Primary key: no Foreign key: Yes Foreign key table: VISIT_DETAIL Foreign key domain: NA | ||
| observation_source_value | This field houses the verbatim value from the source data representing the Observation that occurred. Data type: varchar(50) Required: no Primary key: no Foreign key: no Foreign key table: NA Foreign key domain: NA | ||
| observation_source_concept_id | If the OBSERVATION_SOURCE_VALUE is coded in the source data using an OMOP supported vocabulary put the concept id representing the source value here. If not available, set to 0. Data type: integer Required: yes Primary key: no Foreign key: yes Foreign key table: CONCEPT Foreign key domain: NA | ||
| unit_source_value | This field houses the verbatim value from the source data representing the unit of the Observation that occurred. This code is mapped to a Standard Condition Concept in the Standardized Vocabularies and the original code is stored here for reference. Set it to blank (NULL). Data type: varchar(50) Required: no Primary key: no Foreign key: no Foreign key table: NA Foreign key domain: NA | ||
| qualifier_source_value | This field houses the verbatim value from the source data representing the qualifier of the Observation that occurred. This code is mapped to a Standard Condition Concept in the Standardized Vocabularies and the original code is stored here for reference. Data type: varchar(50) Required: no Primary key: no Foreign key: no Foreign key table: NA Foreign key domain: NA | ||
| observation_event_id | If the Observation record is related to another record in the database, this field is the primary key of the linked record. Set it to blank (NULL) Data type: bigint Required: no Primary key: no Foreign key: no Foreign key table: NA Foreign key domain: NA | ||
| obs_event_field_concept_id | If the Observation record is related to another record in the database, this field is the CONCEPT_ID that identifies which table the primary key of the linked record came from. Set it to 0 (zero). Data type: bigint Required: no Primary key: no Foreign key: Yes Foreign key table: CONCEPT Foreign key domain: NA | ||
| value_as_datetime | d_lab_sent_resu_dist | Populate this variable from the d_lab_sent_resu_dist, the date on which the lab has sent the result to district. | Store it in YYYY-MM-DD HH:MM:ss format. Since no time is specified in the source data so set it to midnight (00:00:00). It is possible that some Observation records might store a result as a date value. Data type: datetime (datetime without timezone) Required: no Primary key: no Foreign key: no Foreign key table: NA Foreign key domain: NA |