@ericaVoss and @Patrick_Ryan I will make a template using both the TRUVEN report and the Rabbit in a Hat output. I’ll send it to you Erica as soon as it is done.
Finally got around to finalizing a first set up. @ericaVoss How could I send this to you for review? (I can’t upload a Word document here)
@ericaVoss The v5 version of the spec refers to the source_to_concept_map table in section 3. Since that table is no longer populated as part of the standard vocabulary files downloaded from Athena, can you post the SQL you use to build it? When tracking down mapping issues, everything hinges on the “source-to-standard” linkage. I know how it should be built, but it would be good for the spec to be self-contained.
Here is a view that I use to create an expanded source to concept map using
v5 vocabulary. It comes in handy when checking mappings. Syntax is for
RedShift (Postgres)
CREATE OR REPLACE view source_to_concept_map_5
( source_vocabulary_id, updated, source_concept_id, source_code,
source_code_description, source_domain, source_standard_concept
, invalid_reason
, target_concept_id, target_vocabulary_id, target_concept_name,
target_domain, target_standard_concept
)
AS
WITH
source_concepts
AS
( SELECT * FROM concept
WHERE vocabulary_id IN( ‘ICD9CM’, ‘ICD9Proc’, ‘CPT4’, ‘HCPCS’, ‘ICD9CM’,
‘LOINC’, ‘NDC’, ‘ICD10’, ‘ICD10CM’, ‘GPI’ )
)
SELECT s.vocabulary_id AS source_vocabulary_id, s.updated
, s.concept_id AS source_concept_id
, s.concept_code AS source_code
, s.concept_name AS source_code_description
, s.domain_id AS source_domain
, s.standard_concept AS source_standard_concept
, rel.invalid_reason AS invalid_reason
, t.concept_id AS target_concept_id
, t.vocabulary_id AS target_vocabulary_id
, t.concept_name AS target_concept_name
, t.domain_id AS target_domain
, t.standard_concept AS target_standard_concept
FROM – Check for Replacement
( SELECT s.concept_code
, COALESCE( t.concept_id, s.concept_id ) AS concept_id
, COALESCE( t.concept_name, s.concept_name ) AS concept_name
, COALESCE( t.domain_id, s.domain_id ) AS domain_id
, COALESCE( t.standard_concept, s.standard_concept ) AS
standard_concept
, CASE WHEN t.concept_id IS NOT NULL THEN 1 ELSE 0 END AS Updated
, s.vocabulary_id
FROM source_concepts s
LEFT OUTER JOIN concept_relationship rel ON concept_id_1 =
s.concept_id AND relationship_id = ‘Concept replaced by’
LEFT OUTER JOIN concept t ON t.concept_id = concept_id_2
) s
LEFT OUTER JOIN concept_relationship rel ON concept_id_1 = s.concept_id
AND relationship_id = ‘Maps to’
AND rel.invalid_reason IS NULL
LEFT OUTER JOIN concept t ON t.concept_id = concept_id_2;
Thanks @ericaVoss and @DTorok .
I had my blinders on and could not see what was right in front of my eyes in the spec in 3.1.2!