Using Normalizer Transformation, generating Extra Column

Using Normalizer Transformation, generating Extra Column:

This scenario discussed in Facebook group, hence thought of to get solution.

Below was actual requirement.

location,sales

mumbai,100,200,600

bengaluru,200,205,300

Kolkatta,150,100,200

hydrabad,300,250,200

and required target was

Steps to create mapping

Step1: First import/create flat file definition in Source Analyzer

Step2: Create target relational table definition in target designer

Step3: Now let’s create normalizer Transformation to convert column into rows

And Edit Transformation to create port under Normalizer tab “location” with occurrence ‘0’ and “sales” with occurrence ‘3’, after this informatica create to default port “GKID” and “GCID”.

As we know if you have created any column occurrence other than ‘0’ it will create GKID and GCID correspond to that port.

The Normalizer transformation has a generated column ID (GCID) port for each multiple-occurring column. The generated column ID is an index for the instance of the multiple-occurring data. For example, if a column occurs four times in a source record, the Normalizer returns a value of 1, 2, 3, or 4 in the generated column ID based on which instance of the multiple-occurring data occurs in the row.

The naming convention for the Normalizer generated column ID is GCID_<occuring_field_name>.

GKID works as initialize sequence for GCID.

Step4: Now let’s create expression transformation to cleanse the data as well as to generate new column “month” into target, Will pull location, sales and GCID column from Normalizer T/R to expression and use below logic

Location: LTRIM(RTRIM(LOCATION))

SALES_0: TO_INTEGER(SALES)

MONTH: IIF(GCID_SALES=1,'JAN',IIF(GCID_SALES=2,'FEB','MAR'))

Step5: Drag and drop all output column from Expression to target and validate mapping and save it.

Step6: Create session and workflow provide all connection strings and run workflow below is the output achieved from this mapping.

Target look like this

Please provide your comment below.