Changing text value to whole number value
I am finding that I can upload the same custom dimension in multiple datasets and sometimes it comes through as a text value, others as a whole number value. This becomes troublesome because I am trying to join those datasets and even when I try to alter the value type in Magic ETL it is still resulting in an error.
Any ideas on how to prevent or fix this?
Comments
-
@kvallee - Depending on the company, sometimes all the data in a single column is actually not all the same.
One example is Google - CPC data. This column many time comes in with a $ value, but will sometimes come in as the word "NULL" and sometimes as "Avg .36". Both of which are strings, so that makes the entire column a string column.
I've had to create DataFlows / ETLs to edit and CAST these columns to what they are supposed to be. Sadly it is the Data the company provides to Domo and how they provide it that causes this issue.
0 -
feels same issue here. I am getting
"Failed to convert data 'USD 440,000' to type Integer for column 'ACV Amount at Risk'
when on Magic Etl. Basically, I wanted to convert that field that is Text into a Number so I can report the $ value. Very new in DOMO so not finding ways to get this use case resolve
0 -
@jbuaron You'd probably have to remove the "USD" currency code portion of your values in order to make the datatype change you want. You can do a text replace transform in magic ETL to remove any instance of letter characters and replace it with nothing so you are left with only numbers. Then do your text to number transform.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1 -
thanks for the speedy response and that able to eliminate the USD. However, when I use now the Set Column Type, seems I am getting similar error -- please find attached screenshot.
Any idea how I can move forward?
Thank you in advance.
0 -
Looks like the space is still at the front: '_44..' instead of '44..'.
Try removing the space in your other transform, too, and see how that goes.
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive