Normalizer Transformation in Informatica with EXAMPLE

Normalizer is an active transformation, used to convert a single row into multiple rows and vice versa. It is a smart way of representing your data in more organized manner.

If in a single row there is repeating data in multiple columns, then it can be split into multiple rows. Sometimes we have data in multiple occurring columns. For example

In this case, the class score column is repeating in four columns. Using normalizer, we can split these in the following data set.

Step 1 – Create source table "sales_source" and target table "sales_target" using the script and import them in Informatica

Download the above Sales_Source.txt File

Step 2 – Create a mapping having source "sales_source" and target table "sales_target"

Step 3 – From the transformation menu create a new transformation

1. Select normalizer as transformation
2. Enter name, "nrm_sales"
3. Select create option

Step 4 – The transformation will be created, select done option

Step 5 – Double click on the normalizer transformation, then

1. Select normalizer tab
2. Click on icon to create two columns
3. Enter column names
4. Set number of occurrence to 4 for sales and 0 for store name
5. Select OK button

Columns will be generated in the transformation. You will see 4 number of sales column as we set the number of occurrences to 4.

Step 6 – Then in the mapping

1. Link the four column of source qualifier of the four quarter to the normalizer columns respectively.
2. Link store name column to the normalizer column
3. Link store_name & sales columns from normalizer to target table
4. Link GK_sales column from normalizer to target table

Save the mapping and execute it after creating session and workflow. For each quarter sales of a store, a separate row will be created by the normalizer transformation.

The output of our mapping will be like –

The source data had repeating columns namely QUARTER1, QUARTER2, QUARTER3, and QUARTER4. With the help of normalizer, we have rearranged the data to fit into a single column of QUARTER and for one source record four records are created in the target.

In this way, you can normalize data and create multiple records for a single source of data.