transformations:rollup
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
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 #1
Total the number of awards won, per team, based on the students' award count.
Before (source table)
Student | Team | Awards |
---|---|---|
Barry | Blue | 7 |
Tina | Gold | 12 |
Sonja | Gold | 8 |
Derrick | Blue | 11 |
Blue | ||
Gold |
After (result table)
Student | Team | Awards |
---|---|---|
Barry | Blue | 7 |
Tina | Gold | 12 |
Sonja | Gold | 8 |
Derrick | Blue | 11 |
Blue | 18 | |
Gold | 20 |
Action parameters
Primary key: Student
Parent key: Team
Amounts: Awards
Result: Update column with results
Example #2
Generate cumulative totals for each level of the chart of accounts shown below.
Before (source 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 |
Utilities Expense | Expense | 10000 |
Advertising | Expense | |
Print Media | Advertising | 4000 |
Online/Digital Media | Advertising | 8000 |
Asset | ||
Expense |
After (result table)
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 |
Utilities Expense | Expense | 10000 | 10000 |
Advertising | Expense | 12000 | |
Print Media | Advertising | 4000 | 4000 |
Online/Digital Media | Advertising | 8000 | 8000 |
Asset | 120000 | ||
Expense | 67000 |
Action parameters
Primary key: Description
Parent key: Account Type
Amounts: Amount
Result: Create new column (Totals)
See also
transformations/rollup.txt · Last modified: 2025/01/28 10:02 by yurii