Link

Measurement

Reading from procedures.csv

Destination Field Source field Logic Comment field
measurement_id      
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    
measurement_datetime date Use 00:00:00 as the time.  
measurement_time date    
measurement_type_concept_id     Use concept_id 5001 for all records
operator_concept_id      
value_as_number      
value_as_concept_id      
unit_concept_id      
range_low      
range_high      
provider_id      
visit_occurrence_id encounter Lookup visit_occurrence_id using encounter, joining to temp table defined in AllVisitTable.sql.  
visit_detail_id      
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      
value_source_value      

Reading from observations.csv

Destination Field Source field Logic Comment field
measurement_id      
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 5001 for all records
operator_concept_id      
value_as_number value    
value_as_concept_id      
unit_concept_id      
range_low      
range_high      
provider_id      
visit_occurrence_id code Lookup visit_occurrence_id using encounter, joining to temp table defined in AllVisitTable.sql.  
visit_detail_id      
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