Changing text value to whole number value

kvallee

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?

Bulloko

@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

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

jbuaron

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

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

jbuaron

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

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.

jbuaron

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