How to alter a column in text and cast data type as integer?

JJ14
JJ14 Member
in Magic ETL

How to alter a column that is in text and cast data type as integer or floating decimal?

I get this error when using the Alter Columns tile: 'Failed to parse text ' ' from column 'Column A' as type 'Integer'

How can I alter the columns data type in the magic ETL successfully?

Answers

  • GrantSmith
    GrantSmith Coach

    Because you have empty strings it doesn't know how to interpret it as a number. You can try and use a formula tile to convert it to null which should then allow you to convert it.

    NULLIF(TRIM(`Column A`), '')

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • JJ14
    JJ14 Member
    https://community-forums.domo.com/main/discussion/comment/85122#Comment_85122

    now I get the error 'Failed to parse text 'Column A' from column 'Column A' as type 'Integer'

  • GrantSmith
    GrantSmith Coach

    You need to use the backticks (`) instead of the apostrophes ('). Backticks represent a column name; apostrophes are for literal string values, so when it's 'Column A', you're getting the actual string Column A instead of the value inside the field.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach

    You can also just click on the cog next to the cast data type in the Alter Columns tile and under Bad Values, choose Null. The tile will then convert all the values to integer that are actually integers and then null out all the values that don't convert.

    image.png

    **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.
  • German_Rodriguez
    German_Rodriguez Member

    following i was able to fix it

    (NULLIF(TRIM(Column_name), '')) * 1

Categories