Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

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

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

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

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

NicoleJohnson
ACE Emeritus
ACE Emeritus

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

 

Weekly Challenge Image 303.pngWe 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!

Aaron_Harter
11 - Bolide

Great use case for a Macro!

Spoiler
1.png

Macro Interior:2macro.png

 

Luke_C
17 - Castor
Spoiler
Luke_C_0-1642521581417.png

 

Spoiler
Luke_C_1-1642521599401.png

 

iheffner
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
iheffner_0-1642524550001.png

 

binuacs
20 - Arcturus
Spoiler
binuacs_0-1642527699201.png
Spoiler
binuacs_1-1642527779596.png

 

patrick_digan
17 - Castor
17 - Castor
Spoiler
patrick_digan_0-1642529681074.png

 

RolandSchubert
16 - Nebula
16 - Nebula
Spoiler
303W.jpg
Spoiler
303M.jpg
AngelosPachis
16 - Nebula

That was a great challenge, thanks @NicoleJohnson !

 

 

AngelosPachis_1-1642531894247.png

 

Macro's Interface: 

AngelosPachis_3-1642531944293.png

 

Macro:

Spoiler
AngelosPachis_2-1642531916841.png

 

 

 

hayleematecko
8 - Asteroid

Fun workflow!

geoff_zath
Alteryx
Alteryx

Workflow

Spoiler

geoff_zath_0-1642541003078.png

Macro

Spoiler

geoff_zath_1-1642541028178.png