Issue converting string to number in beast mode

Options
jlazerus
jlazerus Member
in Magic ETL

I've seen some others posts about this and have gotten this to work on other cards but I'm having an issue converting a string to a number on this card. I've tried `Field_name`*1 and I've tried CAST(`Field_Name` AS DECIMAL(10,2)) but I get back a 0 or null response in the newly created column. I've also tried a CASE statement to exclude null or blank fields. Is there any way to do this?

 

Original column: Credit_Final

New Column: Credit Final (or whatever)

 

1.png

 

Any help is greatly appreciated.

 

 

Best Answer

  • rahul93
    rahul93 Contributor
    Answer ✓
    Options

    the column you want to convert to a numeric/decimal column contains the character '$' and doing this wont be possible unless you remove the character. Also, I have never used a cast function in a beast mode and i dont believe it exists. 

    To replace the '$' character you can use the replace function and then multiply it by 1, you can also add a condition for null.

    Eg: ifnull(REPLACE(`field_name`,'$',''),0) * 1

     

    However, I would advise you to do this conversion in the dataflow or workbench when you are loading the dataset.

     

    Let me know if you have any questions.

Answers

  • rahul93
    rahul93 Contributor
    Answer ✓
    Options

    the column you want to convert to a numeric/decimal column contains the character '$' and doing this wont be possible unless you remove the character. Also, I have never used a cast function in a beast mode and i dont believe it exists. 

    To replace the '$' character you can use the replace function and then multiply it by 1, you can also add a condition for null.

    Eg: ifnull(REPLACE(`field_name`,'$',''),0) * 1

     

    However, I would advise you to do this conversion in the dataflow or workbench when you are loading the dataset.

     

    Let me know if you have any questions.

Categories