A relational table includes the quarterly unit sales for each store. To calculate the annual unit sales for each store, use a Normalizer transformation to create a row for each quarter. Then, use an Aggregator transformation to aggregate the quarterly sales for each store.
On the Normalized Fields tab, create a normalized field called "QuarterlySales." To indicate that this field represents four fields, set the occurs value to four.
To include the store number data in the mapping, from the menu, select Generate From Incoming Fields and select StoreNo. Use the default occurs value of one because this field does not include multiple-occurring data.
The following image shows the Normalized Field tab after adding both fields:
Notice that when you set the QuarterlySales occurs value to four, the Normalizer creates the generated column ID field and the generated key field.
Configure the Normalizer field mappings
On the Field Mapping tab of the Normalizer transformation, connect the incoming fields to the normalized fields.
In the Normalized Fields list, the Normalizer replaces the multiple-occurring QuarterlySales field with corresponding fields to hold the normalized data: QuarterlySales_1, QuarterlySales_2, QuarterlySales_3, and QuarterlySales_4. The list also includes the StoreNo field.
Connect the incoming fields to the StoreNo and QuarterlySales normalized fields as follows:
Configure the Aggregator transformation
To calculate the annual sales by store, add an Aggregator transformation to the mapping and connect the Normalizer to the Aggregator.
In the Aggregator transformation, use the default All Fields rule to pass all fields from the Normalizer to the Aggregator.
To group data by store number, add a group by field on the Group By tab, and then select the StoreNo field.
The following image shows the Group By tab with the StoreNo group by field:
On the Aggregate tab, create a Decimal output field named AnnualSales_byStore. To configure the output field, use the QuarterlySales field in the following aggregate expression: SUM(QuarterlySales). The QuarterlySales field represents all of the normalized quarterly data.
The following image shows the Aggregate tab with the AnnualSales_byStore output field:
Configure the Target
Add a Target transformation and connect the Aggregator transformation to the Target transformation.
Use the default All Fields rule to pass all fields from the Aggregator to the Target transformation.
On the Target tab, select the target connection and the target object.
On the Field Mapping tab, the incoming fields list includes the AnnualSales_byStore field created in the Aggregator transformation, and the StoreNo field that passed through the mapping from the source.
The incoming fields list also includes the QuarterlySales and generated key columns created by the Normalizer. These fields do not need to be written to the target.
Connect the StoreNo and AnnualSales_byStore fields to corresponding target fields.
The following image shows the configured Field Mapping tab:
Task results
When you run the task, the mapping task normalizes the source data, creating one row for each quarter. The task groups the normalized data by store, and then aggregates the quarterly unit sales for each store.
