Changing text value to whole number value

kvallee
kvallee Member
in Archive

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

  • Bulloko
    Bulloko Domo Employee

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

  • kshah008
    kshah008 Contributor

    @kvallee, did Bulloko's reply help answer your question?

  • jbuaron
    jbuaron Member

    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 :(

  • AS
    AS Coach

    @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"
  • jbuaron
    jbuaron Member

    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.

     

     

  • AS
    AS Coach

    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"
  • jbuaron
    jbuaron Member

    @AS removing the space is tricky lol - took me awhile and by just entering a blank value (space) solved it! thanks

Categories