GL Account Mapping Roll-up Help!!

Question

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

Alteryx SAP IFRS Roll-up example.xlsx

csmith11 · Accepted Answer

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

Here's an example below:

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.

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

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.

Please let me know if you have any questions.