Any way to filter a column by data type?

Options
HowDoIDomo
HowDoIDomo Contributor
edited December 2024 in Datasets

I am working with a data set that has multiple contributors. Unfortunately many of them enter data their own way.

Specifically, I am looking at Diabetic A1C data. One person might enter it is as 5.765, another 5.76%, yet another 5.764444E3, and then another would enter something as 93 MG/L.

Is there any formula or DOMO tool that I can use to only accept the decimal version of this data? Or, can I tell DOMO to convert bad data into nulls so I can get rid of them? It's annoying that when someone enters data wrong, it can no longer be read as a number.

Tagged:

Answers

  • ggenovese
    ggenovese Coach

    In Magic ETL you can write a REGEX expression to strip out everything that's not a number or number-related,

    REGEXP_REPLACE(your value,'[^0-9,.]','')

    however an additional problem is that 5.76% would get turned into 5.76 which is a totally different number. So I think your idea to turn bad data to nulls is safer. You can do this with an Alter columns tool, cast the data to a decimal and click the gear to add the option to convert bad values to NULL

    image.png

Categories