User Tools

Site Tools


transformations:rollup

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 #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

Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki