Extracting from multi-layer Trial Balance

[adragotoiu]

Hi,

I'm working with a multi-tiered/layered trial balance and I'd like to be able to extract only the analytical accounts (the deepest members of the hierarchy within each individual account).

Is this possible within Alteryx ?

I've attached an excel file showing the input, the logic behind the split and what the output should be.

Please note:

1) accounts 102 and 103 in the example are treated as both synthetic and analytical accounts as there are no further sub-accounts in their respective hierarchy

2) account numbers can have various formats, include strings, commas or other separators etc; i'm trying to figure out if a universal solution to this type of data extraction exists.

Thanks,

[danilang · Accepted solution]

Hi @adragotoiu 

Base on your description and input files, I've come up with this solution to generate your split table

The main program adds a record ID and standardizes the field type before adding a blank Parent Field.  The Build Hierarchy macro determines the parent of each row of data.  Once the parent has been determined, the Childs Rows filter in combination with Children of children Summarize tool handle find the children that have children, i.e. 1042, since these are treated differently.  After joining back to the main data and unioning the unmatch records, the Generate rows tool creates an extra row for each existing row.  The following formula tool determines the type of row, Analytic or Synthetic and also the CB value needs to be added to it.  The Crosstab and Dynamic Rename tools convert the data to the 2 column format found in the Split table.

The iterative Build Hierarchy macro takes the account from the first row of data, and appends it to the record set. The formula tool determines if any account names start with this one.  If so it's marked as the parent for that row.  The select cleans up the columns and the 2 Sample tools send the 1st row to the data output and the rest to the Iterative output.  This output loops back to the input so the remaining records can then be processed.  Since every iteration removes one row from the input, the macro will end after all the rows are processed.  The macro is currently set with an iteration limit of 10000 rows, so if you have more than that in your input, you'll need to adjust it.

The final results of this match your split table

 

This process is built on the premise that the child account names start with parent account name along with a modifier, like your input list and that the list is already sorted.  The account names can contain any digits, characters, spaces, commas, etc but any child account must start with exact string that identifies the parent.

Dan

[adragotoiu]

@danilang

That's really good. Thank you !

[adragotoiu]

Follow-up: when i'm trying to apply the workflow to a slightly larger file (not close to the iteration limit) i get an error Error: Hierarchy (9): Iteration #1: Tool #5: There were more than 16 records in the source.

What would i need to tweak ?

Thank you,

[adragotoiu]

Figured it out, it was the warn on too many records being generated option in the append.

[danilang]

hi @adragotoiu 

Glad you found it.  

Dan