Measurement

Measurement

Reading from procedures.csv

Destination Field	Source field	Logic	Comment field
measurement_id		Autogenerated
person_id	patient	Lookup in the person table: map by mapping person.person_source_value to patient.
measurement_concept_id	code	Use code to lookup target_concept_id in CTE_TARGET_VOCAB_MAP: select ctvm.target_concept_id from procedures pr join cte_target_vocab_map ctvm on ctvm.source_code = pr.code and ctvm.target_domain_id = ‘Measurement’ and ctvm.target_vocabulary_id = ‘SNOMED’ and ctvm.target_standard_concept = ‘S’ and ctvm.target_invalid_reason is NULL
measurement_date	date (v2.7.0) start (v3.0.0)
measurement_datetime	date (v2.7.0) start (v3.0.0)	Use 00:00:00 as the time.
measurement_time	date (v2.7.0) start (v3.0.0)
measurement_type_concept_id			Use concept_id 32827 (EHR encounter record) for all records
operator_concept_id		Set to 0 for all records
value_as_number		cast(null as float)
value_as_concept_id		Set to 0 for all records
unit_concept_id		Set to 0 for all records
range_low		cast(null as float)
range_high		cast(null as float)
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.
measurement_source_value	code
measurement_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 procedures pr on csvm.source_code = pr.code and csvm.source_vocabulary_id = ‘SNOMED’
unit_source_value		cast(null as varchar)
value_source_value		cast(null as varchar)

Reading from observations.csv

Destination Field	Source field	Logic	Comment field
measurement_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.
measurement_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 = ‘Measurement’ and ctvm.target_standard_concept = ‘S’ and ctvm.target_invalid_reason is NULL
measurement_date	date
measurement_datetime	date	Use 00:00:00 as time.
measurement_time
measurement_type_concept_id			Use concept_id 32827 (EHR encounter record) for all records
operator_concept_id		Set as 0 for all records
value_as_number	value
value_as_concept_id	value	units	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.value and ctvm.target_domain_id = ‘Meas value’ and ctvm.target_standard_concept = ‘S’ and ctvm.target_invalid_reason is NULL
unit_concept_id	units	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.units and ctvm.target_vocabulary_id = ‘UCUM’ and ctvm.source_vocabulary_id = ‘UCUM’ and ctvm.target_standard_concept = ‘S’ and ctvm.target_invalid_reason is NULL
range_low		cast(null as float)
range_high		cast(null as float)
provider_id	encounters.provider_id
visit_occurrence_id	code	Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql.
visit_detail_id	code	Lookup visit_occurrence_id using encounter, joining to temp table defined in final_visit_ids.sql and add 1000000
measurement_source_value	code
measurement_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 observations o on csvm.source_code = o.code and csvm.source_vocabulary_id = ‘SNOMED’
unit_source_value	units
value_source_value	value