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.
2022.1.1.30569 Patch Release Update

The 2022.1.1.30569 Patch/Minor release has been removed from the Download Portal due to a missing signature in some of the included files. This causes the files to not be recognized as valid files provided by Alteryx and might trigger warning messages by some 3rd party programs. If you installed the 2022.1.1.30569 release, we recommend that you reinstall the patch.

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

NicoleJohnson
15 - Aurora
15 - Aurora

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

 

Weekly Challenge Image 304.pngThe International Bank of Awesome Data (IBAD) leadership team has been impressed with your work so far in helping to clean up the data extracts from their various bank branches. But now they want to get to the good stuff – the reporting & analytics.

 

Each IBAD bank branch reports data up to their Assistant to the Regional Manager, and we’ve been tasked with helping them out with this data reporting (acting as Secret Assistants to the Regional Manager, if you will). The Assistant to the Regional Manager is determined by a combination of Region and the first 3 digits of the Bank Branch ID, with the mapping file provided. All Assistants to the Regional Manager want to receive the same summary statistics by branch – Opening Balance, Debits, Credits, Ending Balance, Avg Daily Balance, % Change, and Count of Customers throughout the month.

 

Should you choose to accept this challenge, your task will be to create a batch macro that will generate customized individual output data sets for each Assistant to the Regional Manager, summarizing these metrics by Bank Branch as well as overall for each Assistant to the Regional Manager.

- Step 1: Identify the Assistant to the Regional Manager for each Bank Branch based on Region & the first 3 digits of the IBAD Bank Branch ID
- Step 2: Create a batch macro ** that will calculate the required metrics by bank branch as well as overall for each Assistant to the Regional Manager
- Step 3: Output should be a unique YXDB file for each Assistant to the Regional Manager with all the metrics for their branches (refer to the format in the output example)

 

HINT: To calculate the avg. daily balance, you will need to add daily debits/credits, including $0 on days where there are no transactions. Generate Rows tool + Multi-Row field will likely be your friends here…

 

** Yes, we realize you could also probably do this without a batch macro, just by using some of the grouping functions within various tools… but that’s not as much fun, is it?? **

Aaron_Harter
11 - Bolide

Fun challenge!

Spoiler
I'm calculating different values for total Debits / Credits / Ending Balance in this solution.

0.pngBatch Macro Interior: 
1macro.png


2.png

RolandSchubert
16 - Nebula
16 - Nebula

Great challenge! 

 

Spoiler
304W.jpg
Spoiler
304M.jpg
Kenda
15 - Aurora
15 - Aurora
Spoiler
Kenda_0-1643045735124.png

 

Spoiler
Kenda_1-1643045785324.png

 

balajilolla1
8 - Asteroid
Spoiler
Solution Attached

balajilolla1_0-1643053110444.png

 

Spoiler
Macro
balajilolla1_1-1643053135989.png

 

Reesetrain2
8 - Asteroid

All,

 

Spoiler
I used:
4 Joins
1 Generate Rows
5 Formula Tools
6 Summarize Tools
6 RecordID Tools
-My numbers did not come out the same as the authors....
Macro.PNG

Workflow.PNG

 

PhilipMannering
15 - Aurora
15 - Aurora

My numbers were also slightly different for Avg Daily Amount. Looking forward to seeing the official solution.

 

Spoiler
PhilipMannering_0-1643113804438.png
PhilipMannering_1-1643113827195.png

 

 

 

 

AkimasaKajitani
15 - Aurora
15 - Aurora

My solution.

 

But some fields are not different from the sample output.

Spoiler

AkimasaKajitani_0-1643122826448.png

AkimasaKajitani_1-1643122838513.png

 

 

 

davidhardister
8 - Asteroid

davidhardister_1-1643151029709.png

 

 

davidhardister_0-1643151010986.png

 

Watermark
8 - Asteroid

KISS 

MT Solution 304.png

 

Ok, not really so simple  ....  

 

 

Spoiler
MT Solution 304 Macro.pngBonus hint: Know which branches belong to which mgr., trust the check values.