Convert text into a number

Valeriehern
Valeriehern Member
in Magic ETL

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

  • shaanarora
    shaanarora Member
    Answer ✓

    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.

  • Valeriehern
    Valeriehern Member
    Answer ✓

    That worked! Thank you!

  • NFSharma
    NFSharma Member
    Answer ✓

    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.

Answers

  • shaanarora
    shaanarora Member

    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.

  • kshah008
    kshah008 Contributor

    @Valeriehern, did shaanarora's reply help you out?

  • Valeriehern
    Valeriehern Member

    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 

     

     

  • shaanarora
    shaanarora Member
    Answer ✓

    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.

  • Valeriehern
    Valeriehern Member
    Answer ✓

    That worked! Thank you!

  • DomoBeaver
    DomoBeaver Member

    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!

  • shaanarora
    shaanarora Member

    @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?

  • jbuaron
    jbuaron Member

    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.

  • dwcreagh
    dwcreagh Member

    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

  • NFSharma
    NFSharma Member
    Answer ✓

    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.

  • user03239
    user03239 Member

    How can i use this function in domo dimenssion level.

  • user03239
    user03239 Member

    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.

  • NFSharma
    NFSharma Member

    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.

  • NFSharma
    NFSharma Member

    @DomoBeaver You can convert that column's datatype in a workflow to whole number ?

  • user09445
    user09445 Member

    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. 

     

Categories