Convert text into a number
Hello,
I have a problem with numeric data being recognized in Domo as text (word) data when I'm trying to build my card. I know this is the problem because the data (which is numbers) is placed under CATEGORIES and it is listed with a T symbol.
I need help with a Beast Mode formula that will translate this data into numeric values so it falls under the VALUES with the "123" symbol next to it.
Please help.
Thank you,
Valerie
Best Answers
-
Yes that is probably the issue. You can try this but I have not verified that it will work.
SUM(REPLACE(`number_column`,',',''))
This replaces all commas with an empty string, which essentially removes all commas. The SUM function should then be able to parse your column as a number instead of a string.
4 -
That worked! Thank you!
1 -
This works.
Otherwose DOMO treats the 1st comma as the decimal point and rounds up rest of the number.
Example: 1,053, 233 will be treated as 1.00 when you perform mathematical operations.
REPLACE(`number_column`,',','') takes all the ',' off and makes the text 1053233, that is treated like a number.
0
Answers
-
If there are definitely no non-number values in the column, then using SUM(`Column Name`) should convert all the rows in that column to numbers.
0 -
@Valeriehern, did shaanarora's reply help you out?
0 -
Hi there,
Thank you for the help, but this did not work for me. Do you think the problem is that the numeric values include a comma. Example: 12,500 ?
-Valerie
0 -
Yes that is probably the issue. You can try this but I have not verified that it will work.
SUM(REPLACE(`number_column`,',',''))
This replaces all commas with an empty string, which essentially removes all commas. The SUM function should then be able to parse your column as a number instead of a string.
4 -
That worked! Thank you!
1 -
I still cannot make this work. I have a column of text that is made up of numbers but domo is reading it as text. I need it to be read as numbers. When I use the solution above it will not work on a chart.
Please help and thanks!
0 -
@DomoBeaver Can you provide a sample of the column you're trying to convert to a number? Is it possible that there are nulls in it?
0 -
tried
RIGHT(`Forecasted/Actual Churn Value`,2)*1
or
SUM(REPLACE(`ACV Amount at Risk`,',',''))
and both not working on my end or Im doing wrong. The RIGHT commands output something but wrong value - meaning to small on the real $ value while the SUM command is not outputting anything at all.
Please find the attached screenshot.
0 -
I saw this as I was searching for a similar solution. Here is how I solved it. I create a Magic ETL dataset to keep this complexity away from my business users.
Our Salesforce outputs converted $ amounts like:
USD 12,500
I added a Replace Text Step to and ETL as:
Column Find Replace
Amount USD Empty String
Amount , Empty String
Amount (\.[0-9][0-9]) (Regex) Empty String
Amount <space> Empty String
Then add a Set Column Type step
to convert Amount from Text to Decimal.
Hope that helps,
Dennis
0 -
This works.
Otherwose DOMO treats the 1st comma as the decimal point and rounds up rest of the number.
Example: 1,053, 233 will be treated as 1.00 when you perform mathematical operations.
REPLACE(`number_column`,',','') takes all the ',' off and makes the text 1053233, that is treated like a number.
0 -
How can i use this function in domo dimenssion level.
0 -
I have a dimension that is actually number formate but domo showing Text(T). So i need to convert that text(T) into Number(123)..
Give me the replay how to do it.
0 -
This seems to be a problem with the data source. Just check if your source dataset is setting the right datatypes. If you cannot correct that, then change the column datatype in a workflow and power your card with the transformed dataset.
0 -
@DomoBeaver You can convert that column's datatype in a workflow to whole number ?
0 -
This is what I did and it worked for transforming a column datatype from text to number as my original dataset could not be corrected I would have to do it in ETL workflow.
Note that my original data even though was a text type it did not contain any text character but strictly number. It was just incorrectly recorded as text type data.
So I started with Text Formatting and Only Show Numbers in this step, and then Set Cloumn Type to Whole Number in the next step, and voila you got Account Number (my column name) transformed to what supposed to be a numeric datatype.
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
- 60 App Studio
- 41 Variables
- 689 Automate
- 177 Apps
- 454 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 127 Manage
- 124 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