Table of Contents

Search

  1. Preface
  2. Transformations
  3. Source transformation
  4. Target transformation
  5. Aggregator transformation
  6. Cleanse transformation
  7. Data Masking transformation
  8. Deduplicate transformation
  9. Expression transformation
  10. Filter transformation
  11. Hierarchy Builder transformation
  12. Hierarchy Parser transformation
  13. Hierarchy Processor transformation
  14. Input transformation
  15. Java transformation
  16. Java transformation API reference
  17. Joiner transformation
  18. Labeler transformation
  19. Lookup transformation
  20. Mapplet transformation
  21. Normalizer transformation
  22. Output transformation
  23. Parse transformation
  24. Python transformation
  25. Rank transformation
  26. Router transformation
  27. Rule Specification transformation
  28. Sequence Generator transformation
  29. Sorter transformation
  30. SQL transformation
  31. Structure Parser transformation
  32. Transaction Control transformation
  33. Union transformation
  34. Velocity transformation
  35. Verifier transformation
  36. Web Services transformation

Transformations

Transformations

Mapping example with a Normalizer and Aggregator

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 mapping includes a Source transformation connected to a Normalizer transformation, an Aggregator transformation, and then to a Target transformation.
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:
The Normalized Fields tab includes the two added fields as well as a GCID_QuarterlySales field and a GK_QuarterlySales field.
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:
The Field Mapping tab shows the source fields and normalized fields connected as follows: Q1 to QuarterlySales_1, Q2 to QuarterlySales_2, Q3 to QuarterlySales_3, Q4 to QuarterlySales_4, and StoreNo to StoreNo.

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:
The Group By tab shows StoreNo as the 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:
The Aggregate tab shows the AnnualSales_byStore output field with the aggregate expression.

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:
The Field Mapping tab of the Target transformation shows the StoreNo field from the Aggregator transformation mapped to the StoreNo field in the target and the AnnualSales_byStore field mapped to the SalesbyStore field.

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.
The task writes the following data to the target:
StoreNo SalesbyStore
1001 208 1022 485 1190 445