Aggregate
Summarize data by aggregating.
Summarize data by aggregating.
The aggregate operations are: MAX, MIN, COUNT, SUM, AVERAGE, MEDIAN, OR, AND, and STDEV.
You can use the OR and AND functions only with columns that contain Boolean values. The tool returns TRUE if:
OR is used and at least one record is true.
AND is used and all records are true.
Tip
You can use the COUNT operation without selecting an aggregate column.
When to use this tool
Use this tool when you want to:
Perform an aggregate operation on a column.
Arrange data into groups.
Arrange your data into groups AND apply an aggregate operation.
Configuration
Use the following configuration options to configure the Aggregate tool.
In Pipe builder, add your data source.
Click
+Add Tool.Click See all tools.
In the search bar, search for Aggregate. Click Add tool.
Tip
You can also find the Aggregate tool in the Calculate section.
Connect the tool to your data set.
In the configuration pane, enter the following information:
Table 31. Aggregate tool configurationField
Description
Aggregate columns Group by
Select the Group by checkbox if you want to group the aggregate columns by a specific existing column. The Aggregate tool currently supports Group by NULL for all data types:
Text
Numeric
Date
Boolean
Column
Select the column to use from the drop-down menu.
Sum
Select the sum to use from the drop-down menu.
Column name
Enter a name for the column.
(Optional) Add additional aggregate columns.
Usage example
Let's use the aggregate tool to return the total row count for our data set.
Leave the Grouped columns and Aggregate columns fields blank.
Type a name for the new column.
In the Operation field, select COUNT.
Aggregate tool updates
Important
Updates for the Aggregate tool are not widely available yet. If you want this feature, contact Varicent Support.
The Aggregate tool currently has two versions, with the difference in Group by configuration functionality:
Updated tool functionality: The Group by option in the configuration retains any NULL values in the output for
DateorBooleancolumns.For example, you have a data set with with NULL values in a
Datecolumn namedTransaction Dateand aBooleancolumn namedOnline:
The NULL values from those columns are retained in the output when grouped by these two columns:
Caution
If you intend to upgrade the Aggregate tool, we recommend that you create a copy of your pipe. Saving your pipe gives you the chance to access to your prior version. To save your work:
Create a copy of your pipe from the Pipes page.
Open version history in your pipe and save a version.
Older tool functionality: The Group by option in the configuration removes any NULL values in the output for
DateorBooleancolumns.For example, you have a data set with with NULL values in a
Datecolumn namedTransaction Dateand aBooleancolumn namedOnline:The NULL values from those columns are removed in the output when grouped by these two columns:
