I need to create a vocabulary mapping between ICD10AM and SNOMED. I have a downloaded a mapping table from this link https://www.ihacpa.gov.au/resources/icd-10-am-and-achi-mapping-tables. I used the twelfth edition. I currently have in the concept table and concept_relationship tables in our database a mapping between ICD10 and SNOMED. I am trying to use the ICD10 to map the SNOMED codes to the ICD10AM using the mapping tables I downloaded. The issues I found was that some terms have one to many or many to many relationships in the downloaded tables and when I use SQL code to link the downloaded mapping tables to the existing ICD10 to SNOMED duplicated entries are created in the concept table and duplicated relationships. So for instance Z84.8 ICD10AM code with description “Family history of other specified conditions” Maps to Z84.8 ICD10 Code and also maps to Z81.1, Z81.2, Z81.3, Z81.4 and in this mapping has a slightly different code_name -“Family history of disorder of other specified conditions”. The result is that there are 2 entries in the concept table ICD10AM for Z84.8 with 2 different names and in the concept_relationship table there are several mappings created for each of the concept_ids. So when we look to map those in the conditions_occurrence table there will be several condition records created for one condition. How would you suggest to do this mapping? There is also complications when it comes to mapping additive and partial codes how would you suggest to map those as well? I know some of you have experience in mapping ICD10CN to SNOMED using ICD10 how did you deal with those issues when you created that mapping.
I think your workflow has some severe limitations.
First, as I understood from your post, you are trying to map ICD10AM codes to SNOMED. For this task, you are not supposed to use any mapping tables from SNOMED to ICD10 (any version). There are reasons why all authoring organizations provide mappings from ontology A to ontology B, and from ontology B to ontology A separately. So you need to pick the right direction and go from the ICD site to SNOMED.
Second, the longer the way between ontologies (in your case ICD10AM to ICD10 to SNOMED), the more mapping discrepancies you collect on the way. So please make sure that you review everything and I propose using the mappings you get this way only as mapping candidates - to review and accept them if you are lucky, or to fix them if you are not.
Third, you don’t need to put the whole mapping chain into the concept_relationship table. You only need your source and target concepts. This approach should remove duplicates.
This is up to you to fix. So you need to pick the correct targets for your concepts.