Table of Contents

Search

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

0 COMMENTS

We’d like to hear from you!