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)