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
This week we are launching a new partnership with the ACE program. @NicoleJohnson created a series of weekly challenges that will be posted in the next four weeks starting today. We hope you have fun!
The International Bank of Awesome Data is a new bank that prides itself on its ability to focus on the most important thing of all: THE DATA! With bank branches all over the world, IBAD is truly a global force to be reckoned with… er, they would be, if they could actually read their data.
Turns out, data is messy, even really really awesome data, and before you can do anything with it, you have to clean it up. So let’s start out with some basic data preparation exercises, using some sample bank transaction files submitted to IBAD headquarters for monthly reporting by a dozen of their bank branches from around the world.
THE CHALLENGE:
Each branch has apparently decided on their own format for their data extracts… so while each file “technically” contains the necessary information for our monthly reporting, the regions all have different formatted files that will need to be parsed out & cleaned up into a consistent format.
Some rules we need to apply to standardize our data outputs:
- Remove leading 0’s and/or trailing whitespace from all fields EXCEPT Customer ID (Customer ID should always be 12 characters, NO SPACES, with leading 0’s as needed)
- Bank Account Number should always follow the pattern of 6 digits – 8 digits – 2 digits (123456-12345678-12)
- Transaction DateTime should be in standard Alteryx datetime format (yyyy-mm-dd HH:MM:SS), and the data type for the field should be DateTime
- If there is not a Debit/Credit field, you can assume it is Credit (C) if there is a - sign present in the Amount field, otherwise it should default to Debit (D). **
- Transaction Cleared Indicator data type should be Boolean
** No modifications are required to the Amount and Opening Balance fields for now - we will clean up those fields in next week’s challenge! **
Thank you @NicoleJohnson ! I figured out all of the bonus/extra credit as well except for how to append the Bank ID - I couldn't seem to figure out the logic.
