ROLL UP

Category: Transform / Advanced

Description

This action adds amounts up through each level of a hierarchy structure.

Use cases

Generate total (summed) values for each level of a hierarchy (i.e., accounting/chart of accounts, employee hierarchy structure, etc.)

Action settings

Setting	Description
Primary key	Select the column containing the unique key values.
Parent key	Select the column containing the parent keys.
Amounts	Select the column containing the values to be summed.
Result	Choose whether the rolled-up values will appear in the column selected in "Amounts" or in a new column (and provide a column name).

Remarks

All keys in the selected Primary key column must be unique.

Be sure the parent-child relationship between the primary keys and parent keys does not create a recursive relationship (e.g. Key01's parent is Key10, and Key10's parent is Key01). This will result in an error.

Examples

Example: Generate cumulative totals for each level of the chart of accounts shown below.

Input table:

Description	Account Type	Amount
Cash	Asset	50000
Accounts Receivable	Asset	10000
Inventory	Asset
Blue Widgets	Inventory	40000
Yellow Widgets	Inventory	20000
Cost of Goods Sold	Expense	45000
Advertising	Expense
Print Media	Advertising	4000
Online/Digital Media	Advertising	8000
Utilities Expense	Expense	10000
Asset
Expense

Action parameters:

Primary key: "Description"
Parent key: "Account Type"
Amounts: "Amount"
Result: Create new column ("Totals")

Result:

Description	Account Type	Amount	Totals
Cash	Asset	50000	50000
Accounts Receivable	Asset	10000	10000
Inventory	Asset		60000
Blue Widgets	Inventory	40000	40000
Yellow Widgets	Inventory	20000	20000
Cost of Goods Sold	Expense	45000	45000
Advertising	Expense		12000
Print Media	Advertising	4000	4000
Online/Digital Media	Advertising	8000	8000
Utilities Expense	Expense	10000	10000
Asset			120000
Expense			67000

EasyMorph Help

Table of Contents