Table of Contents

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

SettingDescription
Primary keySelect the column containing the unique key values.
Parent keySelect the column containing the parent keys.
AmountsSelect the column containing the values to be summed.
ResultChoose 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


See also