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 toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Categories
- 7.7K All Categories
- 7 Connect
- 922 Connectors
- 245 Workbench
- 477 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 57 Visualize
- 205 Beast Mode
- 2.1K Charting
- 8 Variables
- 4 Automate
- 349 APIs & Domo Developer
- 84 Apps
- Workflows
- 15 Predict
- 3 Jupyter Workspaces
- 12 R & Python Tiles
- 242 Distribute
- 60 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 43 Product Ideas
- 1.1K Ideas Exchange
- 3 Community Forums
- 16 Getting Started
- 3 Community Member Introductions
- 50 Community News
- 18 Event Recordings
- 576 日本支部