Converting lab results into preferred unit at ETL time

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

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

3

There was email discussion last week that revives this old UnitMorph code and knowledge base.

@clairblacketer and @Patrick_Ryan - please chime in.

It would be great to know that once we re-run ThemisUnits study with latest 2025 data, that we are doing this all for a nice final cherry on top of truly building it into the tool. (or even CDM spec itself (the idea of targeting unit standardization) (for subset of tests). And truly strongly recommending units for a set of top 50 (or any other topN number).

The challenge of expert driven consensus vs. data-driven consensus still remains. Few years ago, I pursued the data-driven consensus since there is no “voting”/“personal preference” involved.

Also tagging @Christian_Reich

As a survey call to community - please reply if you are currently standardizing units (and converting numberical values) at ETL time for any (or for subset) of lab tests?

v.5.4 has now the missing column we needed back in 2018-2020