Replace text and value mapper failing

dacorson

I have a couple data flows that use replace text or value mappers to remove certain text characters from a number column to then convert to an integer. Examples of text we are finding in our source data are "#DIV/0" and "--". However, when those characters don't exist at all in the source data, the replace text tile fails and we have to go in and remove the offending column from the replace tile. It seems like rather than failing, the tile should just recognize that the value is not there and complete. Is there a setting I am missing in the tiles? It seems like every other time this data flow fails and we are pulling data from Amazon, so I know they won't "fix" there report for me. I suppose we could do a find replace in Excel before loading the file to remove the characters, but I would think Domo has something to handle this. Let me know if you have any ideas.

MarkSnodgrass

I would suggest moving away from the replace text and value mapper tiles and use the formula tile in your ETL. It is much more flexible and you can do all your work in a single tile.

You can do something like this in the formula tile.

CAST(
CASE WHEN `field` = '#DIV/O' THEN 0
WHEN `field` = '--' THEN 0
ELSE `field`
END as INTEGER)

You can add additional WHEN statements for other variations that your data might have. You might also want to use the TRIM function around your field in case there are spaces.

Another option is this where it tests to see if it can convert it to an integer and using the number if it can, otherwise it will make it a zero.

CAST(
CASE WHEN TRY_CAST(`field` as INTEGER) > 0 THEN `field`
ELSE 0
END as INTEGER)

You could use NULL instead of 0 in your ELSE statement if that is more appropriate.

Hope this helps.

MarkSnodgrass

I would suggest moving away from the replace text and value mapper tiles and use the formula tile in your ETL. It is much more flexible and you can do all your work in a single tile.

You can do something like this in the formula tile.

CAST(
CASE WHEN `field` = '#DIV/O' THEN 0
WHEN `field` = '--' THEN 0
ELSE `field`
END as INTEGER)

You can add additional WHEN statements for other variations that your data might have. You might also want to use the TRIM function around your field in case there are spaces.

Another option is this where it tests to see if it can convert it to an integer and using the number if it can, otherwise it will make it a zero.

CAST(
CASE WHEN TRY_CAST(`field` as INTEGER) > 0 THEN `field`
ELSE 0
END as INTEGER)

You could use NULL instead of 0 in your ELSE statement if that is more appropriate.

Hope this helps.