alteryx Community

# Alteryx Designer Desktop Discussions

## Amounts in different formats - Thousands and Decimal separator

8 - Asteroid

Hi,

I am trying to build a logic to parse the amounts which are present in the source data in the below formats.

Format-1 : Comma is used as thousand separator and Dot is used as decimal separator. e.g. 1,234.456

Format-2 : Dot is used as thousand separator and Comma is used as decimal separator. e.g. 1.234,456

Approach : The amounts are read as string and then regex match is used to identify the format. After format is identified, the commas are removed and decimal point is put in the appropriate place. For e.g. if Input amount is 1.234,456, output is 1234.456.

Challenge: The logic breaks when the input amount is e.g. 123.345. Because "." could be decimal or thousand separator.

It results in ambiguity when pattern is used to match the amount format.

Thanks,

Mohith

7 REPLIES 7
22 - Nova

Looking at the challenge i don't think there is an easy solution for this 😅 data lacks consistency

Alteryx Alumni (Retired)

Hi @MohithRai ,

You can try to create a Macro similar to the Currency Converter in our Public Gallery. See below

Thanks,

Mike

18 - Pollux

are there always 3 units after the decimal point? If not - this is rough and really a ML exercise...

8 - Asteroid

Amounts can be a whole number or can contain decimal. It can have any number of units after decimal point.

8 - Asteroid

Hi All,

I was trying with the below workaround and it pretty much generates the expected output. It is a bit inefficient ,but accurate as of today🙂.

Assumption:

At least one of the amount value in the source data is present in the format that contains both 'thousand separator' and 'decimal separator'  i.e. 1,234.56(Format-1) or 1.234,56(Format -2).

Approach: Before parsing the amounts,

• Data is scanned to check if any one of the amount value matches the format as per our assumption.
• If any one of the amount matches our assumption, then that implies the source data follows either Format-1 or Fromat-2.
• Using this result we create an identifier('Format-1' or 'Format-2') and append it to the source data.
• Using the identifier the amounts are parsed.

Output:-

Scenario-1:

Scenario-2:

This approach fails when the source data does not have any amount in the format as per our assumption. This is a rare scenario, when all the amounts are in hundreds or less than that.