Applying string formula to numeric value

[garyaquaq]

Hi all,

I have some currency data that I import which is either formatted as 555.34 or 555,34. It changes month to month but I want to change the comma's into decimal notation eveytime.

I use the following formula:

IIF(IsString([Field]), Replace([Field], ",", "."), [Field])

This only work when the data comes in as a string i.e. it contains a comma seperater. But the formula fails when the data comes in as a double i.e. decimal seperater when it should just pass through as the else part of the formula.

Could anyone shed any light as to why alteryx works like this or any other ways to do this?

Thanks

[Kenda]

Hey @garyaquaq!

I would recommend using a Formula tool. You could create a new field or just edit the existing one. If you make a new field, you likely want to make it a double. Use the below expression to replace all commas with a decimal point:

REGEX_Replace([Field1], ",", ".")

where [Field1] is the field you want to edit. Hope this helps!

[MarqueeCrew · Accepted solution]

Hi @garyaquaq

For your specific formatting, you can use this formula:

Replace(ToString([Field]),",",'.')

Alteryx works with data types and allows great flexibility with them.  If a field starts out with a numeric type, it can readily by converted to a string or other type of data.  In fact, the auto field tool will modify the incoming fields to their most efficient data type.

Cheers,

Mark

[garyaquaq]

Cheers all,

ToNumber(Replace(ToString([Field]),",",'.'))

This formula works regardless if the input is a string or a double with both seperators.

Gary