Changing data from text to integer

Dodgeball_Australia
Dodgeball_Australia Member
in Magic ETL

Hey there,

I have a Webhook data coming from Wix that looks like the following "VIC Player Hosting Fee (April Cloth Camp) AUD 87.00". I need to seperate the monetary value at the end of the string and convert from text to integer, but have tried using methods like CAST and the Alter Columns MagicETL tile to no avail. Any suggestions of best practice here?

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    REGEXP_REPLACE(`field`, '^.* ([^ ]+)$', '$1') * 1


    this should find the last value in your string after the last space. Multiplying it by 1 will convert it to a number.

    Will the numbers be always at the end or anywhere in the string preceded by AUD?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • david_cunningham
    david_cunningham Coach

    Have you already separated out the monetary value from the string?

    If not, my recommendation would be to figure out a consistent way to identify the location of the value you want to extract, and then use Regex to get that value.

    You’ll want to make sure there is no white space around the extracted value

    from there converting to a numeric value should be possible using the alter tile

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    REGEXP_REPLACE(`field`, '^.* ([^ ]+)$', '$1') * 1


    this should find the last value in your string after the last space. Multiplying it by 1 will convert it to a number.

    Will the numbers be always at the end or anywhere in the string preceded by AUD?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Dodgeball_Australia
    Dodgeball_Australia Member

    Hey Grant, they will always be at the end of the string, following "AU". I'll give this a try, cheers!

  • Dodgeball_Australia
    Dodgeball_Australia Member

    @GrantSmith I'm getting the error "Failed to convert value 'Fee' from type 'String' to type 'Floating Decimal'.

  • German_Rodriguez
    German_Rodriguez Member

    hi, i am not sure what i am missing but This calculation is using a nonexistent function

  • david_cunningham
    david_cunningham Coach

    @German_Rodriguez - there are a couple of functions that work that are not listed in the official documentation. There are also functions that exist in MagicETL, that do not exist in Beast Mode (and vice versa). In this case, REGEX_REPLACE is a listed MagicETL function.

    David Cunningham

    ** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
    ** Did this solve your problem? Accept it as a solution! ✔️**

Categories