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

JJ14 · 2023-12-19T19:04:24+00:00

There was an error rendering this rich post.

JJ14

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?

Find more posts tagged with

Accepted answers

All comments

GrantSmith

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`), '')

JJ14

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

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.

MarkSnodgrass

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.

German_Rodriguez

following i was able to fix it

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