observation.Rmd| Destination Field | Source field | Logic | Comment field |
|---|---|---|---|
| observation_id | Autogenerated | ||
| person_id | patient | Map by mapping person.person_source_value to patient. Find person.person_id by mapping allergies.patient to person.person_source_value. | |
| observation_concept_id | code | Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from allergies a join cte_target_vocab_map ctvm on ctvm.source_code = a.code and ctvm.target_domain_id = ‘Observation’ and ctvm.target_vocabulary_id = ‘SNOMED’ and ctvm.target_standard_concept = ‘S’ and ctvm.target_invalid_reason is NULL | |
| observation_date | start | ||
| observation_datetime | start | Use 00:00:00 as time. | |
| observation_type_concept_id | Set as 38000280 (Observation recorded from EHR) for all records. | ||
| value_as_number | cast(null as float) | ||
| value_as_string | cast(null as varchar) | ||
| value_as_concept_id | Set as 0 for all records | ||
| qualifier_concept_id | Set as 0 for all records | ||
| unit_concept_id | Set as 0 for all records | ||
| provider_id | encounters.provider | ||
| visit_occurrence_id | encounter | Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql. | |
| visit_detail_id | encounter | Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql and add 1000000. | |
| observation_source_value | code | ||
| observation_source_concept_id | code | Use code to lookup source_concept_id in CTE_SOURCE_VOCAB_MAP: select csvm.source_concept_id from cte_source_vocab_map csvm join allergies a on csvm.source_code = a.code and csvm.source_vocabulary_id = ‘SNOMED’ | |
| unit_source_value | cast(null as varchar) | ||
| qualifier_source_value | cast(null as varchar) | ||
| value_source_value | cast(null as varchar) | ||
| observation_event_id | cast(null as bigint) | ||
| obs_event_field_concept_id | cast(null as int) |
| Destination Field | Source field | Logic | Comment field |
|---|---|---|---|
| observation_id | Autogenerated | ||
| person_id | patient | Map by mapping person.person_source_value to patient. Find person.person_id by mapping encouters.patient to person.person_source_value. | |
| observation_concept_id | code | Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from conditions c join cte_target_vocab_map ctvm on ctvm.source_code = c.code and ctvm.target_domain_id = ‘Observation’ and ctvm.target_vocabulary_id = ‘SNOMED’ | |
| observation_date | start | ||
| observation_datetime | start | ||
| observation_type_concept_id | Set as 38000280 (Observation recorded from EHR) for all records. | ||
| value_as_number | cast(null as float) | ||
| value_as_string | cast(null as varchar) | ||
| value_as_concept_id | Set as 0 for all records | ||
| qualifier_concept_id | Set as 0 for all records | ||
| unit_concept_id | Set as 0 for all records | ||
| provider_id | encounters.provider | ||
| visit_occurrence_id | encounter | Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql. | |
| visit_detail_id | encounter | Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql and add 1000000. | |
| observation_source_value | code | ||
| observation_source_concept_id | code | Use code to lookup source_concept_id in CTE_SOURCE_VOCAB_MAP: select csvm.source_concept_id from cte_source_vocab_map csvm join conditions c on csvm.source_code = c.code and csvm.source_vocabulary_id = ‘SNOMED’ | |
| unit_source_value | cast(null as varchar) | ||
| qualifier_source_value | cast(null as varchar) | ||
| value_source_value | cast(null as varchar) | ||
| observation_event_id | cast(null as bigint) | ||
| obs_event_field_concept_id | cast(null as int) |
| Destination Field | Source field | Logic | Comment field |
|---|---|---|---|
| observation_id | Autogenerated | ||
| person_id | patient | Map by mapping person.person_source_value to patient. Find person.person_id by mapping encouters.patient to person.person_source_value. | |
| observation_concept_id | code | Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from observations o join cte_target_vocab_map ctvm on ctvm.source_code = o.code and ctvm.target_domain_id = ‘Observation’ and ctvm.target_vocabulary_id = ‘LOINC’ and ctvm.standard_concept_id = ‘S’ and ctvm.target_invalid_reason is null | |
| observation_date | date | ||
| observation_datetime | date | ||
| observation_type_concept_id | Set as 32827 (EHR encounter record) for all records. | ||
| value_as_number | cast(null as float) | ||
| value_as_string | cast(null as varchar) | ||
| value_as_concept_id | Set as 0 for all records | ||
| qualifier_concept_id | Set as 0 for all records | ||
| unit_concept_id | Set as 0 for all records | ||
| provider_id | encounters.provider | ||
| visit_occurrence_id | encounter | Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql. | |
| visit_detail_id | encounter | Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql and add 1000000. | |
| observation_source_value | code | ||
| observation_source_concept_id | code | Use code to lookup source_concept_id in CTE_SOURCE_VOCAB_MAP: select csvm.source_concept_id from cte_source_vocab_map csvm join observatitons o on csvm.source_code = o.code and csvm.source_vocabulary_id = ‘LOINC’ and csvm.source_domain_id = ‘Observation’ | |
| unit_source_value | cast(null as varchar) | ||
| qualifier_source_value | cast(null as varchar) | ||
| value_source_value | cast(null as varchar) | ||
| observation_event_id | cast(null as bigint) | ||
| obs_event_field_concept_id | cast(null as int) |