We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
alteryx Community

# Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
###### IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback

## Challenge #303: International Bank of Awesome Data - Part II

ACE Emeritus

A solution to last week’s challenge can be found here.

We learned our lesson last week at the International Bank of Awesome Data (IBAD) – data is less than awesome if it isn’t standardized & clean. So now that we have our data standardized, we have to tackle the remaining outlier – the Amount fields.

Being that IBAD is an international bank of such great renown, they happily accept currency of all forms – dollar, peso, euro, franc, pound, yen, kuna, krona, Monopoly money, etc. However, different countries/regions/banks have different norms when it comes to applying numeric formats to the amounts they report back to headquarters. Some use symbols or currency codes to indicate the currency type, some put negative signs on the right instead of the left, some use commas instead of periods for the decimal indicator, etc. Additionally, these banks report customer balances in the customer's local currency (rather than the bank's local currency) so a conversion will be necessary to get all our amounts into the same currency (USD, for reporting purposes).

OUR CHALLENGE:
Since we have to do this same exercise A LOT (cleaning up number formats & converting to USD), we have been asked to create a standard macro that will perform these steps. Since they come in all shapes & sizes, our Number Formatter macro needs to be flexible enough to deal with a variety of different number format scenarios.

THINGS THE MACRO SHOULD DO FOR ANY AMOUNT FIELD SELECTED:
Step 1: Remove dollar signs and/or any other non-numeric characters (letters, symbols other than decimals/commas, spaces)
Step 2: Based on the Debit/Credit field, apply a negative sign to the Amount field if there isn't one there already (AND make sure it is on the left side of the number value)
Step 3: Look up the current conversion rate to USD using the CurrencyLookupList, and include both the “USD Exchange Rate” and “Decimal Indicator” fields
Step 4: If Decimal Indicator field is a comma, swap the commas for periods so that the decimal indicator is a period for all records
Step 5: Final "Exchanged Amount" should be numeric data type Fixed Decimal with 6 decimal places, converted using the Exchange Rate provided for that Currency Code.

The end goal should be a standard macro that can be used to clean up the Amount field on all 3 of the regional files (whether separate or combined). One indication that you're in good shape will be if you can run the workflow/macro without receiving any conversion errors or warnings!

* For an extra challenge, build your macro in a way that will allow you to run it for the Opening Balance field as well, with an option to NOT apply the Debit/Credit logic!

11 - Bolide

Great use case for a Macro!

Spoiler

Macro Interior:

17 - Castor
Spoiler

Spoiler

7 - Meteor

First time creating my own macro! I found that it was easier to convert the value to a FixedDecimal early to spend less time on string manipulations.

Spoiler

17 - Castor
Spoiler
Spoiler

17 - Castor
Spoiler

16 - Nebula
Spoiler
Spoiler
16 - Nebula

That was a great challenge, thanks @NicoleJohnson !

Macro's Interface:

Macro:

Spoiler

8 - Asteroid

Fun workflow!

Alteryx

Workflow

Spoiler

Macro

Spoiler