Value_as_number in measurement

[boverdui]

Hello,

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

One of my tables is lab_test. The values in the column test_value I expect to end up in the value_as_number column in the measurement output file for OMOP. However, it's what in the test_type column that ends up in value_as_number, which isn't right.

Cheers,
Bert

lab_test.csv
rules_bert.json
measurement.tsv.xlsx

[AndrewThien]

hi Bert,
Can you show the ScanReport and how did you map the concept 3042810 on Carrot Mapper please?
Thanks!
Thien

[AndrewThien]

Hi there,

If you have some values in the test_value field in the lab_test table, and you add some measurement concepts to those values, Carrot will create mappings to the value_as_number field in the measurement OMOP table.

In your case, you don't have any values in the test_value field in the lab_test table (I can only see 'List truncated' there), so I assumed you didn't put any concepts there. If you didn't, Carrot can't create mapping from the values in the test_value to the value_as_number field in the measurement table.

To avoid 'List truncated' value, I think you can modify the min_cell_count setting if you used WhiteRabbit to make the ScanReport, more details here: https://carrot.ac.uk/carrot_etl_guide#profile-data

Let me know if my answer helps

Thanks

Thien

[boverdui]

Hello Thien,

I redid everything using a new ScanReport in which, as you can see, there are no truncated values anymore. However, I still haven't got any values in the value_as_number column in my measurement.tsv though.

ScanReport.xlsx
measurement.tsv.xlsx

Cheers,
Bert

[AndrewThien]

Hello,
Did you put any concepts with the measurement domain to the values in the test_value field of the table lab_test? I put an example and dummy output JSON as below:

Rules - bert 2 - 37 - 2025-03-20 10_15_49.373052.json

If you have done so, and still can't get the values in test_value into the value_as_number in measurement, please give me a shout.

Hope this helps.
Thanks
Thien

[boverdui]

Hi Thien,

But that would mean that if I have hundreds or thousands of different values, I would have to add a concept to each one of them?

Cheers,
Bert

[AndrewThien]

Hi,
So I would suggest you try doing similar to the below then.

Let me know if it helps

[boverdui]

Hi Thien,

That would mean I would need a separate CSV file for every type of measurement, right? Which for some datasets (like my example dataset) would be feasible, but for others not ....

Cheers,
Bert

[prquinlan]

Apologies for reading between the lines and maybe not getting this right, but :)

@AndrewThien on one the comments above, the "list truncated" will probably be because each value is unique, or at leat not enough of them to be above the min count threshold.

@boverdui if I have understood the structure right, you have the "test type" in one column and the value in another? That would mean in the "test value" column then you could have values from multiple different types of test?

If that is right @boverdui then the only current solution is to separate out by column for each measurement type, then you map as @AndrewThien has shown above, where you map all values in a column to a particular measurement.

@AndrewThien for info, this is actually linked to this existing issues: Health-Informatics-UoN/carrot-mapper#874

[boverdui]

@prquinlan

Yes, I have the test type in one column and the value in another. In my example file there are only 3 different tests, so splitting them out into separate files and handling those individually in Carrot is not a problem, but in the case of tens (or hundreds) of different tests (which doesn't seem an unlikely scenario imho), this would become cumbersome.

[prquinlan]

Hi @boverdui great thanks for confirming.

The way Carrot can handle it at the moment is that all results for a particular test have to be in the same column.

There is a request open for the functionality you require, which is to have conditional logic between columns.