Converting to Integer

Kyle_Russell

Hi All,

In my ETL I am loading 2 columns for Credit & Debit, as part of my ETL I want to do Credit-Debit to get a column for amount however the data being loaded is recognising the Credit & Debit as text. I've tried to use the alter columns tile in my ETL to convert to an integer however it doesn't appear to be working.

Any suggestions would be appreciated!

DOMO Failed ETL 2.PNG

DOMO Failed ETL.png

MichelleH

@Kyle_Russell It looks like there are some spaces in the as well. If you wrap a TRIM function around it that should work:

CAST(TRIM(REPLACE(`Amount`,',','')) as DOUBLE)

MichelleH

@Kyle_Russell Could you send some examples of values in those columns? If there are any extra characters, like dollar signs, in the original fields you will need to remove them before you can use the alter columns tile.

Kyle_Russell

Hi Michelle, all values in the fields are numerical with no extra characters, there are commas but this is just the format of excel being exported as general.

DOMO Values Screenshot.PNG

MichelleH

@Kyle_Russell It looks like the commas are causing the numbers to be imported as strings instead of numbers. Try this formula to remove the commas and convert the datatype:

CAST(REPLACE(`Amount`,',','') as DOUBLE)

Kyle_Russell

DOMO Failed ETL Formula.PNG

Looks like the formula is returning the same error as before? Currently using the add formula tile in Magic ETL - is there a more appropriate tile to use?

MichelleH

@Kyle_Russell It looks like there are some spaces in the as well. If you wrap a TRIM function around it that should work:

CAST(TRIM(REPLACE(`Amount`,',','')) as DOUBLE)

Kyle_Russell

That seemed to work, thanks for the help!