Monetary number not converting properly

Hello,

I'm not sure what to do here. I'm working with monetary amounts and for example the "Approved Amount" that returns '2' should actually be 2,175.86. I'm having a hard time figuring out how to approach this.

I'm also getting the error message of "2,175.85 stopped converting at a comma. It might be invalid", end result is this number appears in "Approved Amount" as '2'.

Any help or direction is much appreciated.

Thank you!

image.png

Datasets

Accepted answers

CharlieS

@mbudcst wrote:
Sorry I'm still getting used to Alteryx. When I enter that formula I get "Formula: tried to apply string operator to numeric value (REGEX_REPLACE)

That's ok, the Community is here to help!

Let's talk about that Formula tool:

- it appears that the [ApprovedAmount[ field already exists. One suggestion might be to create a new field called [ApprovedAmount_FixedDec] so that you can obtain the value you want from the [ApporvedAmount] field, and assigned a field type to this new field.

- If a field already exists, the Formula tool cannot change the field type of that field (pro tip: a Multi Field Formula tool can if you want to talk about that). This is why you need to create a new field to assign this new, numeric field type.

*This post was edited to respond to the third image that showed the error.

All comments

CharlieS

Hi @mbudcst

Commas are not valid character is a numeric field, only digits, negative, and periods (unless you specify a different separator for international support). I see in your Formula tool, you replace the "$" with nothing to remove it. That's a great start, but I suggest changing that a bit to this:

ToNumber(REGEX_Replace([ApprovedAmount],"[^\d\.\-]",""))

The RegEx_Replace( function will replace any character that isn't a digit, negative sign, or period with nothing. Also, when it comes to storing currency values, I recommend using the Fixed Decimal numeric field type to avoid any floating point estimation errors.

Check out the attached workflow for an example.

20200312-CurrencyConversion.yxmd

mbudcst

Sorry I'm still getting used to Alteryx. When I enter that formula I get "Formula: tried to apply string operator to numeric value (REGEX_REPLACE)

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167