Converting to Integer
Hi All,
In my ETL I am loading 2 columns for Credit & Debit, as part of my ETL I want to do Credit-Debit to get a column for amount however the data being loaded is recognising the Credit & Debit as text. I've tried to use the alter columns tile in my ETL to convert to an integer however it doesn't appear to be working.
Any suggestions would be appreciated!
Best Answer
-
@Kyle_Russell It looks like there are some spaces in the as well. If you wrap a TRIM function around it that should work:
CAST(TRIM(REPLACE(`Amount`,',','')) as DOUBLE)
1
Answers
-
@Kyle_Russell Could you send some examples of values in those columns? If there are any extra characters, like dollar signs, in the original fields you will need to remove them before you can use the alter columns tile.
1 -
Hi Michelle, all values in the fields are numerical with no extra characters, there are commas but this is just the format of excel being exported as general.
0 -
@Kyle_Russell It looks like the commas are causing the numbers to be imported as strings instead of numbers. Try this formula to remove the commas and convert the datatype:
CAST(REPLACE(`Amount`,',','') as DOUBLE)
1 -
Looks like the formula is returning the same error as before? Currently using the add formula tile in Magic ETL - is there a more appropriate tile to use?
0 -
@Kyle_Russell It looks like there are some spaces in the as well. If you wrap a TRIM function around it that should work:
CAST(TRIM(REPLACE(`Amount`,',','')) as DOUBLE)
1 -
That seemed to work, thanks for the help!
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 603 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 697 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 388 Distribute
- 111 Domo Everywhere
- 271 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 9 Domo University
- 30 Product Releases
- Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive