Part of records in source missing in OMOP

[boverdui]

Hello,

I performed mapping and transformation for some (fake) test date to come to grips with the Carrot system.

60 of the 600 records from my lab_test source table are missing in the output for OMOP, which doesn't seem right.

Cheers,
Bert

lab_test.csv
rules_bert.json
measurement.tsv.xlsx

[AndrewThien]

Hi,

It would be great if you can also share the Scan report and the screenshots of how did you map and add concepts on Carrot Mapper. We will try to re-create the problem based on these and hopefully find out what is going on.
Btw, which ELT tool are you using? is that Carrot Transform or Carrot CDM or something else?

Thanks!

Thien

[anwarfg]

Hi Bert,

I realised my mistake and sent a message just before you replied. I think the issue probably lies with the person file and the person IDs - can you provide your person file, and the command line arguments you used?

Kind regards,
Anwar

[boverdui]

Hi Anwar,

This is what I ran:

poetry run python3 carrot_transform.py run mapstream carrottransform/examples/bert/inputs --rules-file carrottransform/examples/bert/rules/rules_bert.json --person-file carrottransform/examples/bert/inputs/demographic.csv --output-dir carrottransform/examples/bert/test_output --omop-ddl-file carrottransform/config/OMOPCDM_postgresql_5.3_ddl.sql --omop-config-file carrottransform/config/omop.json

And my person file:

person.tsv.xlsx

Cheers,
Bert

[anwarfg]

Hi Bert,

I think I have found the issue. The missing records seem to have a leading '0' in the person id in the lab_test file, but not in the demographics file (e.g., the fifth one, 3094 in the demographics file is actually 03094 in the lab_test file). When it looks for '03094', it only finds '3094', which are not the same. It concludes that it isn't a person you have a record for, and so doesn't add it to the output. It is hard to spot because many viewers will read it as an integer, and automatically remove the leading 0 to view it.

The workaround would be to avoid having leading '0's in the person IDs. I think this should resolve the issue, but I have not checked every one of the records.

Kind regards,
Anwar

[boverdui]

Hi Anwar,

Thanks! That makes sense. Thanks for spotting it!

Cheers,
Bert

[boverdui]

Hi Anwar,

Making sure the person_ids in demographics and lab_test files are formatted the same indeed fixed this issue.

Cheers,
Bert