# Aggregate Transformation

### Overview

The *Aggregate* transformation object provides the functionality to create aggregations of your dataset, using aggregate functions such as Sum, Count, First, Last, Min, Max, Average, Var, or Standard Deviation. The dataset can be split into groups so that the aggregate value(s) can be generated for the group instead of the whole dataset. For example, calculate product count by month of year, or get average sales price by region and year.

{% embed url="<https://youtu.be/M9aILyEphFc?list=PLmyM3TZtVUyJfz1bkpZ-CxJ33fAwHIbug>" %}

### Use Case

In this scenario, we have *Products* data stored in a CSV file. The source file contains information such as *ProductID*, *Supplier ID*, *UnitPrice* of the various products, *QuantityPerUnit* of products available, etc. We want to derive the following information from our source data:

1. Number of products per category
2. Total price of all the products per category
3. Minimum price per category
4. Maximum price per category

We will use the *Aggregate* Transformation object to derive the required information.

### How to Work with Aggregate Transformation

1. From the sources section in the Toolbox, drag and drop a[ *Delimited File Source*](https://documentation.astera.com/astera-data-stack-v9/dataflows/sources/delimited-file-source) object to the dataflow designer.
2. To work with the *Aggregate* Transformation, drag and drop the *Aggregate* Transformation object from *Toolbox > Transformations > Aggregate*.

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FkO9luP0htHJfdFofeovu%2Fgif-drag-and-drop.gif?alt=media\&token=aa2b7298-34c4-493e-8fe9-a3fd44504ce0)

3. Right-click on the transformation object and select *Properties*. The *Layout Builder* window will now open.

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FQcGR0uE9j583PlUVmPq8%2F4.png?alt=media\&token=3f284252-c647-452c-898f-a3a880737f98)

4. Here, you can write the names of the fields that you want to map to the transformation object in the *Name* column and specify the relevant *Aggregate Functions* for them.

For this case:

* *CategoryID*: We will select the *Group-By* option from the *Aggregate Function* drop-down list for this field as we want to group the records based on the available product categories.
* *ProductID*: For this field, we will select the *Aggregate Function* *Count*, in order to calculate the number of products per category.
* *UnitPrice*: We will map this field thrice.

  * To calculate *TotalPricePerCategory,* select the function *Sum* function.
  * To calculate *MaxPricePerCategory,* select the *Max* function.
  * To calculate *MinPricePerCategory,* select the *Min* function.

  ![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FFdw3fikNL9Db9g0q3eqx%2F1.png?alt=media\&token=77239ffa-4cae-42f2-8a10-a134791f58f2)

5. Click on *Next*. The *Aggregate Transformation Properties* window will now open.

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FLr9Clieo0decio4ATLpU%2F5.png?alt=media\&token=07c3b9d9-0694-4ec3-9ca4-684f49f6b5ff)

There are three sorting options in *Aggregate* transformation:

* *Incoming data is pre-sorted on group by fields*: This option *Aggregate* requires data to be sorted by the specified *Group-By* field.
* *Sort Incoming data before building aggregate*: This option will first sort the incoming data, then build its aggregate.
* *Build aggregate using unsorted data*: This option will build aggregate using the incoming data whether it is sorted or not.

6. Click on *Next*. The *Config Parameters* window will now open, where you can further configure and define parameters for the *Aggregate* transformation.

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FgoRRebj2UVqUG7rygSVX%2F6.png?alt=media\&token=8df06aec-6075-4c41-a0ac-a909b1994e1f)

7. Click *Next*. This is the *General Options* window. Click *OK*.

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2F7TDO9v6u3VeR798kQQWn%2F7.png?alt=media\&token=5d2599b8-ee5c-46d8-9155-b6e144592e45)

* *General Options* Window: This window shares options common to most objects in the dataflow.
  * *Clear Incoming Record Messages:* When this option is checked, any messages coming in from objects preceding the current object will be cleared. This is useful when you need to capture record messages in the log generated by the current object and filter out any record messages generated earlier in the dataflow.
  * *Do Not Process Records with Errors:* When this option is checked, records with errors will not be output by the object. When this option is off, records with errors will be output by the object, and a record message will be attached to the record. This record message can then be fed into downstream objects in the dataflow, for example, a destination file that will capture record messages or a log that will capture messages and collect statistics as well.
  * The *Comments* input allows you to enter comments associated with this object.

8. After you have configured the properties, click *OK*.
9. You will see the fields in the object that were added in the *Layout Builder* window.

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FDQbAiwVx9jmEzZOvttVY%2F2.png?alt=media\&token=7f36e72d-6346-47e1-b1dc-6f7ae14a46f5)

10. Map the data fields from the source object to the transformation object. You can auto-map the entire dataset from the source to the transformation object, or only map selected fields that you want to work with. In this case, we will map *CategoryID*, *ProductID,* and *UnitPrice* as those are the fields we want to find aggregations for\*.\*

{% hint style="info" %}
**Note:** The *UnitPrice* field has been mapped three times as these will determine *TotalPricePerCategory, MaximumPricePerCategory,* and *MinimumPriceperCategory*.
{% endhint %}

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2Fm82d4gttw1sX9I80RO8r%2F8.png?alt=media\&token=35aee857-75e8-4f5d-a742-e7c239ce6537)

11. Right-click on the *Aggregate* transformation object and click *Preview Output*.

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FCLZAE9VEVbIIvQ8ymG2V%2Fgif-preview-output.gif?alt=media\&token=8e0108bc-b92f-4cba-bb11-e9b5f578cd97)

12. You will see that the specified *Aggregate Functions* have been applied.

![](https://3891069449-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F28dJIkGHacXhBlux3efx%2Fuploads%2FjUqVSYdvxnME6z6Azwfy%2F3.png?alt=media\&token=bb145a3e-bec0-41ac-b24f-2e3fe1e9fce6)