Page History

This type of data transformation creates a calculated field based on other fields. 

Overview

Using the Aggregate transformation step, you can aggregate your data by changing it into a summary form by applying various functionsThis is done by using the Calculated Field step in the Data Transformation module. Note: This is a built-in step, and therefore will be available in the Transformations List by default.

  Anchoraggregatefunctionsaggregatefunctions

Aggregate Functions

Following are the different types of aggregate functions:

Aggregate Transformation Flow

Step Configuration

Follow the instructions below to configure a calculated field step:

1. Ensure that you have at least one input step containing the data that you want to transform. Once you are ready to include the Calculated Field step in your flow, follow the below instructions: 
2. Expand the Transformation Steps button on the left side of the Transformation Flow builder, to view a list of transformation steps.
3. Drag the Calculated Field step from the list of transformation steps.
   
   Image Added
   
   
4. Connect this step to the previous step in the flow. On doing so, it will become configurable.
5. Click on Add Item.
   
   Image Added
   
   
6. Use the popup to create a calculated field.
   
   Image Added
   
   
7. Update the name of the calculated field.
8. Define your calculation using the buttons at the bottom of the window, for example:
   • Search for the Sum Invoiced Amount field in the -- Select Field -- drop down list and click on it to add it to the formula box.
   • Press the minus ( – ) button.
   • Search for the Sum Cost of Camp field and add it to the formula.
   • You will now have Invoiced Amount - Cost.
   • Test your calculation by using the Validate button. (An error will appear if it is invalid.)
     
     Image Added
     
     
9. If the calculation is valid, click Save. The data preview panel will display a new column field for the result.
   
   Image Added
   
   
10. The step's configuration panel will show the newly created calculated field.
    
    Image Added
    
    

11. You may also edit or delete a calculated field through here.
    
    Image Added

...

Complete Example

Following is an example that shows a full transformation flow involving a calculated field step. This transformation In this example, we will cover how to create a simple transformation flow that aggregates data. This flow will involve setting up an input step, applying the Aggregate calculated field transformation step, and then storing the data into a database. You could always include more steps to your flowsflow. 

1. Click on the Create button in the top-right corner.

2. Then select Transformation Flow. 
   
   

   Note
   If you do not see this option, you may not have security access to transformation flows. Learn how to get access here.

   
   
   
   
   

3. You will be taken to the transformation flow builder.

4. Hover your cursor over the Input Steps input steps button on the left side. A panel with a list of all data extraction steps will appear.
   
   
   
   

5. Drag one of these steps onto the canvas. ( For this procedure, we will use the report single table step as an example. (Click here to learn about all the different input steps.)

6. On doing so, a popup will appear , to choose a report that you want to use in your flow.
   Image Removed
   Select a report by providing its name, and once the column fields appear, click Add Reportload data from a data source.
   
   Image Added
   
   

7. Click on the data source that you require.

8. Then choose the database table, and click on Submit. 
   
   Image Added
   
   

9. The selected table's .
   Image Removed
   The report fields will appear in the transformation flow panel to be configured. 

10. When in the Configure tab, select Select only the fields that you want data to be extracted from.
    Image Removed
    Image Added
    
    

11. You can make further changes, such as renaming the step, adding a description, etc.

12. Once you’re done with the step configuration, click on the Apply button. 
    
    

    Warning
    You must click on the Apply button to save the configuration details. If Otherwise, if you do not click on this button, and click somewhere else first, you will lose the configuration details.

    
    
    

13. On doing so, the data preview panel will display the data extracted from the configured data sourcedatabase table.  
    Image Removed
    Image Added
    
    

14. 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. 
    Image Removedabout this.

15. You can also add other further steps to your transformation flow. 

16. When you are ready to aggregate your data, follow the steps want to include the calculated field step in your flow, continue with the procedure below.

17. Expand the transformation step steps panel by hovering on its icon in the step builder, and then dragging the Aggregate Calculated Field step onto the canvas. 
    Image Removed
    Image Added
    
    

18. 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 calculated field step, and then drag by dragging a connection point to the aggregate step. (Click here to learn about rules involving connections, and how to break existing connections.)calculated field step.

    
    Image Added
    
    

19. Click on the calculated field Image Removed
    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.
    Image Removed
    Select the aggregate function for the fields that you want to aggregate. The list above explains each of these functions in detail.
    Image Removed
    Once aggregation selection for all the required fields have been made, click Apply. The transformed data will appear in the data preview panel
    Image Removedwil be updated.

20. Click on Add Item.
    
    Image Added
    
    

21. A popup window will appear to create a new calculated field.
    
    Image Added
    
    

22. Update the name of the calculated field.
23. Define your calculation using the buttons at the bottom of the window, for example:
    • Search for the Invoiced Amount field in the -- Select Field -- drop down list and click on it to add it to the formula box.
    • Press the divide ( / ) button.
    • Search for the Cost field and add it to the formula.
    • You will now have Invoiced Amount / Cost.
    • Test your calculation by using the Validate button. (An error will appear if it is invalid.)

24. If the calculation is valid, click Save. 
    
    Image Added
    
    

25. The data preview panel will display a new column field for this formula's result.
    
    Image Added
    
    

26. The step's configuration panel will show the newly created calculated field.
    
    Image Added
    
    

27. You may also edit or delete a calculated field through here, by clicking on its menu.
    
    Image Added
    
    
    

28. You can continue to transform your data further by adding more transformation steps.

29. Once you are read ready to save your data in into a writeable database, follow the steps below.

30. Extend the output step steps panel by hovering on its icon, and drag the SQL database output step onto the canvas.
    
    
    
    

31. Next, connect the aggregate Connect the calculated field step (or the previous step in your flow) to the output step by creating a connection.

32. And then configure the output step through the panel on the right-side. Click here  to  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.
    Image Removed
    Image Added
    
    

33. You can now execute the draft flow by clicking on the run button in the top header menu. (This does a quick execution of the data rows in the data preview panel.)

    
    

34. Or save the flow for a full execution. To do that, click on the Publish button.

    
    
    
    

35. Then provide details in the popup that appears, such as providing a proper name, and selecting rights to access the flow.

    
    Image AddedImage Removed
    
    

36. Finally, click on the Save button.

...