Applying string formula to numeric value

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

Parse

Accepted answers

MarqueeCrew

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

All comments

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

Hi @garyaquaq

For your specific formatting, you can use this formula:

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

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15865

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703