Converting to Integer

Options
Kyle_Russell
Kyle_Russell Member
in Magic ETL

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

Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓
    Options

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

Answers

  • MichelleH
    MichelleH Coach
    Options

    @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
    Kyle_Russell Member
    Options

    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
    MichelleH Coach
    Options

    @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
    Kyle_Russell Member
    Options
    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
    MichelleH Coach
    Answer ✓
    Options

    @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
    Kyle_Russell Member
    Options

    That seemed to work, thanks for the help!

Categories