Any way to filter a column by data type?

HowDoIDomo · 2024-12-20T04:54:02+00:00

There was an error rendering this rich post.

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.

Find more posts tagged with

Filters

Magic ETL

Accepted answers

All comments

ggenovese

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