Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.
Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

GL Account Mapping Roll-up Help!!

maskseed
5 - Atom

I'm trying to format a trial balance that isn't structured correctly. I attached a file showing the inputs and the desired output. I also added screenshots of the file incase that was also helpful. I would like the IFRS codes to be subtotals of all the accounts rolling up into it. Please note that if any account have zero balances I still want them to be included and if a IFRS code doesn't have any associated accounts I still want it to be included too, just like in the example output. I'm usually pretty good with alteryx but for some reason I can't figure this one out. Please help! Thanks!!!!

 

maskseed_0-1642257362512.png

maskseed_1-1642257380298.png

maskseed_2-1642257403752.png

 

 

 

1 REPLY 1
csmith11
11 - Bolide

You'll likely need some sort of parent child field that tracks the relationship between your IRFR codes.

 

Here's an example below:

csmith11_0-1642454529452.png

 

I leveraged this field along with a Generate Row tool and was able to create a row for every subgroup a value should be added into.

 

For example: Acct P283299999 ($75,280,039.62) should be added to Subgroup 2-3-8, Subgroup 2-3, and Subgroup 2.

 

csmith11_1-1642454769220.png

 

The simple part was then to join each record to its appropriate group and then sum up each group amount.

 

 

csmith11_3-1642454899158.png

While the output is not as pretty as a pivot table, it contains the data you are looking for. It should be a great start for your final product.

 

csmith11_4-1642454960771.png

 

 

Please let me know if you have any questions.

 

 

 

Labels
Top Solution Authors