Overview
You can use this transformation to convert your data into a summary form, by applying functions such as, count, count distinct, sum, average, etc. This is ideal for statistical analysis.
This is done by using the Aggregate step in the Data Transformation module. Note: This is a built-in step, and therefore will be available in the Transformations List by default.
Aggregate Functions
The chart below shows the different types of aggregation to transform your data:
Aggregate type | Applicable datatype | Explanation |
|---|---|---|
None | Text, Numeric | This will include the field without applying any changes to it. Such fields will be used for grouping. |
Remove | Text, Numeric | This will remove the field. |
Count | Text, Numeric | This will count all the values in the field. |
Count distinct | Text, Numeric | This will count all the distinct (unique) values in the field. |
Max | Numeric | This will return the maximum value in the field. |
Min | Numeric | This will return the minimum value in the field. |
Sum | Numeric | This will sum all the values in the field. |
Average | Numeric | This will return the average of all the values in the field. |
Step Configuration
Follow the instructions below to configure an aggregate step:
- Ensure that you have at least one input step containing the data that you want to transform. Once you are ready to include the Aggregate step in your flow, follow the below instructions:
- Expand the Transformation Steps button on the left side of the Transformation Flow builder, to view a list of transformation steps.
- Drag the Aggregate step from the list of transformation steps.
- Connect this step to the previous step in the flow.
In the configure panel, select the type of aggregation that needs to be applied to each of the data fields.
See the chart above for a description of each type of aggregation.
- Once the aggregate step has been properly configured, click Apply. You will see the result of the applied aggregation in the data preview panel.
Complete Example
The following example shows a full transformation flow that involves an aggregate step. This flow will involve setting up an input step, applying the Aggregate transformation step, and then storing the data into a database. You could always include more steps to your flows.
Click on the Create button in the top-right corner.
Then select Transformation Flow.
If you do not see this option, you may not have security access to transformation flows. Learn how to get access here.
You will be taken to the transformation flow builder.
Hover your cursor over the Input Steps button on the left side. A panel with a list of all data extraction steps will appear.
Drag one of these steps onto the canvas. (For this procedure, we will use the report step as an example. Click here to learn about the different input steps.)
On doing so, a popup will appear to choose a report that you want to use in your flow.
Select a report by providing its name, and once the column fields appear, click Add Report.
The report fields will appear in the transformation flow panel to be configured.
In the Configure tab, select only the fields that you want data to be extracted from.
You can make further changes, such as renaming the step, adding a description, etc.
Once you’re done with the configuration, click on the Apply button.
You must click on the Apply button to save the configuration details. If you do not click on this button, and click somewhere else first, you will lose the configuration details.
On doing so, the data preview panel will display the data extracted from the configured data source.
You can also apply transformations to fields directly from the data preview panel. Click here to learn more.
A new Fields tab will also appear in the step configuration panel. Use this to manage the data fields that are extracted from the step.
You can also add other steps to your transformation flow.
When you are ready to aggregate your data, follow the steps below.
Expand the transformation step panel by hovering on its icon in the step builder, and then dragging the Aggregate step onto the canvas.
Next, create a connection between the input step (or the previous step, if you've added any other ones) and the aggregate step. Hover over the step, and then drag a connection point to the aggregate step.
Now click on the aggregate step icon to configure it. The transformation flow panel will display each of the incoming fields from the previous steps.
Select the aggregate function for the fields that you want to aggregate.
Click Apply. The transformed data will appear in the data preview panel
You can continue to transform your data further by adding more transformation steps.
Once you are read to save your data in a writeable database, follow the steps below.
Extend the output step panel by hovering on its icon, and drag the SQL database output step onto the canvas.
Next, connect the aggregate step (or the previous step in your flow) to the output step by creating a connection.
And then configure the output step through the panel on the right-side. Click here to learn more about configuring this step.
Note: By default, the output step will be highlighted as red to signify that it contains errors. This is because it has not been configured yet.
You can now execute the draft flow by clicking on the run button in the top header menu.
Or save the flow for a full execution. To do that, click on the Publish button.
Then provide details in the popup that appears, such as providing a proper name, and selecting rights to access the flow.
Finally click on the Save button.