Monetary number not converting properly

[mbudcst]

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!

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

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

[CharlieS · Accepted solution]

---

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

[Stevnated]

Hi, I'm new to Alteryx. It seems absolutely insane to me that handling numbers with commas is so difficult, as in that it requires any thought whatsoever. Is Alteryx not typically meant to use with Excel files? I'm thinking I might have the wrong tool. Almost everything I'm trying to do so far could be done more easily in VBA.

[Cal_A]

I think you are right.  For example, there is a function that you can use within the formula tool called "ToNumber" (https://help.alteryx.com/current/designer/conversion-functions)

The Alteryx guidance says:

"ToNumber accepts strings that can be interpreted as scientific notation double precision. By default, the period is used as the decimal separator."

But, if you try feeding it a number like 22,525,123.99 you will get a "stopped converting at a comma" error.   Even the example in the guidance doesn't actually work

ToNumber("123.456,789", 1, 1, ",") returns 123456.789 as a number. This is because the period is automatically interpreted as the thousands separator, while the comma is specified as the decimal separator via decimalSeparator

When I use this formula, I get 123 as the answer.  The function is simply not able to ignore thousand separators (whether comma or space).

This is pretty poor and I would expect better number handling functionality.

The workaround is fine, but the point is that you shouldn't need to use regex to get standard number formatting (i.e. thousands separators) to work.

---