search

OHDSI / Vocabulary-v5.0

Build process for the OHDSI Standardized Vocabularies. Currently not available as independent release.
The Unlicense
222 stars 75 forks source link

BuildRxE.sql bug introduced by @Fix AVOC-3852 #1039

Open lore-edencehealth opened 3 months ago

lore-edencehealth commented 3 months ago

Describe the problem in content or desired feature When the input for BuildRxE has multiple source data that represents the same ingredient + dose then the sum(prec)::INT4 AS new_prec in ri_combo_prex_fix and rd_combo_prex_fix increases the prec which sometimes makes it no longer find a mapping for ingredient + dose even though it does exist.

How to find it input to reproduce the problem: drug_concept_stage: concept_name,domain_id,vocabulary_id,concept_class_id,concept_code B01AE03_IV_1000.0_ug,Drug,2000000001,Drug Product,B01AE03_IV_1000.0_ug B01AE03_IV_1.0_mg,Drug,2000000001,Drug Product,B01AE03_IV_1.0_mg intravenous,Drug,2000000001,Dose Form,IV argatroban; parenteral,Drug,2000000001,Ingredient,B01AE03

relationship_to_concept: concept_code_1,vocabulary_id,concept_id_2,precedence,conversion_factor IV,2000000001,19082106,1, IV,2000000001,19095915,2, mg,2000000001,8576,, ml,2000000001,8587,, ug,2000000001,8576,,0.001 B01AE03,2000000001,1322207,,

internal_relationship_stage: concept_code_1,concept_code_2 B01AE03_IV_1000.0_ug,B01AE03 B01AE03_IV_1000.0_ug,ug B01AE03_IV_1000.0_ug,ml B01AE03_IV_1000.0_ug,IV B01AE03_IV_1.0_mg,B01AE03 B01AE03_IV_1.0_mg,mg B01AE03_IV_1.0_mg,ml B01AE03_IV_1.0_mg,IV

ds_stage: drug_concept_code,ingredient_concept_code,amount_value,amount_unit,numerator_value,numerator_unit,denominator_value,denominator_unit B01AE03_IV_1000.0_ug,B01AE03,,,1000,ug,1,ml B01AE03_IV_1.0_mg,B01AE03,,,1,mg,1,ml

Expected adjustments adjusting SUM(prec)::INT4 AS new_prec to MIN(prec)::INT4 AS new_prec in rd_combo_prex_fix (and ri_combo_prex_fix) fixes this. Not sure if this might reintroduces the undeterminate query problem from #892 .

Screenshots If applicable, add screenshots to help explain your problem.

Additional context running BuildRxE.sql with sum(prec) gives no existing Clinical Drug Comp, running with min(prec) does give an existing Clinical Drug Comp mapping.

lore-edencehealth commented 3 months ago

@maleman this issue is related to #892 opened by you.

TinyRickC137 commented 3 months ago

Thank you for the input, @lore-edencehealth It is impressive! We will get back to this issue after the release (we have not been running BuildRxE in this release)