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.

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 #302: International Bank of Awesome Data - Part I

NicoleJohnson
14 - Magnetar
14 - Magnetar

Weekly Challenge Image 302.pngA solution to last week’s challenge can be found here.

 

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! **

Aaron_Harter
11 - Bolide

Thanks @NicoleJohnson for the fun challenge!

Spoiler
0.png
Luke_C
15 - Aurora

Fun challenge

Spoiler
Luke_C_0-1641832731144.png

 

RolandSchubert
15 - Aurora
15 - Aurora
Spoiler
302.jpg

Great challenge. especially enjoyed the bonus-

Kenda
15 - Aurora
15 - Aurora

Can't wait for the upcoming weekly challenges in the series!

 

Spoiler
Kenda_0-1641835298090.png

 

davidhardister
8 - Asteroid

davidhardister_0-1641838417166.png

 

patrick_digan
16 - Nebula
16 - Nebula
Spoiler
patrick_digan_0-1641840940486.png

 

ggruccio
ACE Emeritus
ACE Emeritus

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. 

 

Spoiler
ggruccio_0-1641842760297.png

 

PhilipMannering
14 - Magnetar
14 - Magnetar

Solution attached,

 

Spoiler
PhilipMannering_0-1641843271891.png

 

binuacs
12 - Quasar
Spoiler
binuacs_6-1641920680679.png