Mapping Example with a Normalizer and Aggregator
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.
The following image shows the mapping to create:
The source data includes the following rows:
StoreNo Q1 Q2 Q3 Q4 Year
1001 30 50 48 80 2014
1022 100 120 125 140 2014
1190 80 108 123 134 2014
Use the Normalizer transformation to pivot source data before the data passes to the Aggregator transformation, as follows:
StoreNo QuarterlySales
1001 30
1001 50
1001 48
1001 80
1022 100
1022 120
1022 125
1022 140
1190 80
1190 108
1190 123
1190 134
Define the Normalized Fields
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, 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 Configuration 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.
The task writes the following data to the target:
StoreNo SalesbyStore
1001 208
1022 485
1190 445
