Column failed to convert from String to Floating Decimal

verytiredgirl
verytiredgirl Member
in Magic ETL

I have a column that show 2023 sales as "$3,432.12" kind of format which DOMO identified as Text. However, I think I need to get rid of the "$" at the front before using Alter Column Tile to change it into Floating Decimal. How can I do that?

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Did you check to see if the text formatting - only show number tile kept your decimal place? I believe it will remove that as well which will be problematic for you.

    I would suggest using a formula tile and then using two replace functions (one to remove the dollar sign, and the other to remove the commas) followed by try_cast to do all that work in one tile. It would look like this:

    TRY_CAST(REPLACE(REPLACE(myfield,'$',''),',','') as DECIMAL)

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • verytiredgirl
    verytiredgirl Member

    update: I used Text Formatting, show only number → alter the column from String type to Floating Decimal and it worked

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    Did you check to see if the text formatting - only show number tile kept your decimal place? I believe it will remove that as well which will be problematic for you.

    I would suggest using a formula tile and then using two replace functions (one to remove the dollar sign, and the other to remove the commas) followed by try_cast to do all that work in one tile. It would look like this:

    TRY_CAST(REPLACE(REPLACE(myfield,'$',''),',','') as DECIMAL)

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Categories