'maps to value' concepts in ETL

sunnyshang · February 7, 2020, 5:52pm

Hello everyone,
I want to find mapped concepts for V45.1 and V45.11. They both ‘maps to’ ‘Past history of procedure’ which is not an exact match. But the ‘maps to value’ concepts seem good. However ‘maps to value’ concepts are in ‘Procedure’ domain. So how ‘maps to value’ concepts are populated in ‘Procedure’ table in ETL process? In ‘Observation’ table, ‘maps to value’ concepts are populated in ‘value_as_concept_id’ column, where similar column is not in ‘procedure_occurrence’ table. Furthermore, are there standard rules of how to populate ‘maps to value’ concepts?
Thank you very much.

concept_code	concept_name	concept_id	vocabulary_id	domain_id	concept_code_is_standard	relationship_id_to_standard	standard_concept_id	standard_concept_code	standard_concept_name	standard_vocabulary_id	standard_domain_id
V45.1	Postsurgical renal dialysis status	44835472	ICD9CM	Observation	N	Maps to	4215685	416940007	Past history of procedure	SNOMED	Observation
V45.1	Postsurgical renal dialysis status	44835472	ICD9CM	Observation	N	Maps to value	4146536	265764009	Renal dialysis	SNOMED	Procedure
V45.11	Renal dialysis status	44831947	ICD9CM	Observation	N	Maps to	4215685	416940007	Past history of procedure	SNOMED	Observation
V45.11	Renal dialysis status	44831947	ICD9CM	Observation	N	Maps to value	4146536	265764009	Renal dialysis	SNOMED	Procedure

aostropolets · February 7, 2020, 6:04pm

What you actually care about is the domain of Maps to. This one has to be Observation. Maps to value can belong to any domain, you’d still but it in value_as_concept_id.

sunnyshang · February 7, 2020, 6:06pm

Thank you Anna. Even though the standard domain is Procedure, I still need to find them in Observation for ‘Maps to value’. Is that correct? Then do you think the ‘standard_domain_id’ should be changed to ‘Observation’?

QI_omop · February 7, 2020, 6:30pm

The concepts that go into value_as_concept_id fields in OBSERVATION or MEASUREMENT tables can belong to any domains. They don’t have to be in Observation or Measurement domain. So yes. Even though the domain is Procedure, but you can still put it into value_as_concept_id fields in OBSERVATION table.

MPhilofsky · February 7, 2020, 8:22pm

@clairblacketer & @Christian_Reich,

It would be helpful to add this to the conventions for Observation and Measurement:

Christian_Reich · February 9, 2020, 8:09am

Yeah. This is another construction site we really need to start working on:

“Maps to value” is a makeshift solution. It allows splitting one concept into two for the MEASUREMENT and OBSERVATION EAV tables. Right now, it kind of works, but if you had more than a single split into two target concept pairs it would be undefined which “Maps to value” belongs to which “Maps to”. Also, we need to be able to split into other types, like (i) concept and number, (ii) concept and number and unit and (iii) concept and date. And who knows what else. the DRUG_STRENGTH table also is a similar construct, except it is not for mapping, but for reference. @Dymshyts and @rimma came up with a new relationship “Maps to numeric” to cover (ii).

I am thinking of a complex table CONCEPT_SPLIT or so. Thoughts?