OHDSI Home | Forums | Wiki | Github

Converting lab results into preferred unit at ETL time


(Vojtech Huser) #1

I am moving a discussion from email into the forum.
Started by Kristin Kostka.

It is about units.
I am replying to a query suggested by Harold.

prior 2019 discussion is here Themis: subgroup: measurement, procedure

About 1 year ago (per github),
I wrote this query after talking to Christian at Medinfo in Lyon.

link https://github.com/OHDSI/sandbox/blob/master/unitmorph/unitmorphC.sql

select 
	 m.measurement_concept_id
	 ,m.value_as_number * c.factor as new_value_as_number
	 ,c.target_unit_concept_id as new_unit_concept_id
	 ,m.value_as_number
	 ,m.unit_concept_id
	from measurement m 
	 join conv c on
	  m.measurement_concept_id=c.measurement_concept_id 
	  and m.unit_concept_id=c.unit_concept_id
	 where m.value_as_number is not null  

The CONV table already has the “correct” target unit. I don’t get your yellow part of the query.

Conv table https://github.com/OHDSI/sandbox/blob/master/unitmorph/conv.csv


(Vojtech Huser) #2

When we convert some data, we need to store the original unit we converted from.

So to Patrick, we do need to add a column to the CDM.
It is described here


t