Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Informatica Demos
Simulation-driven learning modules
Ideas
Events
Change Request Tracking
Marketplace
English
  • Data Integration
  • All Products

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. Data Services transformation
  9. Deduplicate transformation
  10. Expression transformation
  11. Filter transformation
  12. Hierarchy Builder transformation
  13. Hierarchy Parser transformation
  14. Hierarchy Processor transformation
  15. Input transformation
  16. Java transformation
  17. Java transformation API reference
  18. Joiner transformation
  19. Labeler transformation
  20. Lookup transformation
  21. Machine Learning transformation
  22. Mapplet transformation
  23. Normalizer transformation
  24. Output transformation
  25. Parse transformation
  26. Python transformation
  27. Rank transformation
  28. Router transformation
  29. Rule Specification transformation
  30. Sequence Generator transformation
  31. Sorter transformation
  32. SQL transformation
  33. Structure Parser transformation
  34. Transaction Control transformation
  35. Union transformation
  36. Velocity transformation
  37. Verifier transformation
  38. Web Services transformation

Transformations

Transformations

Back Next

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!