Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.
Announcement | We'll be doing maintenance between 2-3 hours, which may impact your experience. Thanks for your patience as we work on improving the community!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer 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