Changing data from text to integer

Dodgeball_Australia

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?

GrantSmith

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?

david_cunningham

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

GrantSmith

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?

Dodgeball_Australia

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

Dodgeball_Australia

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

German_Rodriguez

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

david_cunningham

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