Replace text and value mapper failing
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.
Best Answer
-
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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
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.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive