Native measurement values not copied to CDM

[turbomam]

Many columns in my cdm_synthea10.measurement table had all null or 0 values at the end of my hybrid ETL (#17)

• value_as_number
• value_as_concept_id
• unit_concept_id
• range_low
• range_high
• provider_id
• unit_source_value
• value_source_value

The person, visit and measurement concept IDs were present, so I just copied the raw values and units back from the native schema.

update
	cdm_synthea10.measurement m2
set
	value_source_value = natob.value,
	unit_source_value = natob.units
from
	cdm_synthea10.measurement m
join cdm_synthea10.visit_occurrence vo on
	m.visit_occurrence_id = vo.visit_occurrence_id
join cdm_synthea10.person p on
	p.person_id = vo.person_id
join native.observations natob on
	natob.patient = p.person_source_value
	and natob.encounter = vo.visit_source_value
	and natob.code = m.measurement_source_value
where
	m2.person_id = m.person_id
	and m2.visit_occurrence_id = m.visit_occurrence_id
	and m2.measurement_source_value = m.measurement_source_value;

Then set the unit concept types as much as possible

update
	cdm_synthea10.measurement m2
set
	unit_concept_id = sc2stvm.target_concept_id
from
	cdm_synthea10.measurement m
join cdm_synthea10.source_to_standard_vocab_map sc2stvm on
	m.unit_source_value = sc2stvm.source_code
where
	m2.person_id = m.person_id
	and m2.visit_occurrence_id = m.visit_occurrence_id
	and m2.measurement_source_value = m.measurement_source_value;

There are some Synthea units that don't get transformed by that query.

unit_source_value	count
	22676
mmHg	20700
{score}	12525
{nominal}	4288
U/L	3402
mL/min/{1.73_m2}	3117
kU/L	1200
{count}	140
{T-score}	82
IU/L	6
ng/dl	4
m[IU]/L	4
M/uL	2

Fix some manually?

update
	cdm_synthea10.measurement m2
set
	unit_concept_id = 8876
where
	unit_source_value = 'mmHg' 

Then populate value_as_number. This could probably be done much more rigorously.

update
	cdm_synthea10.measurement
set
	value_as_number = cast(value_source_value as decimal)
where
	unit_concept_id > 0

A this point there are incomplete conversions due to the units dead ends described above. I haven't done anything with the nominal/descriptive findings that could go in value_as_concept_id. And the following columns remain empty/null/zero:

• value_as_concept_id
• range_low
• range_high
• provider_id

[AnthonyMolinaro]

@turbomam Nice work on this Mark, thanks for staying involved. I've pushed the changes. The range columns are still NULL and provider_id is still 0 since the provider table is empty. The other columns draw their values from source_to_source_vocab_map (if they exist).