Informatca Scenario: 1

Get top 5 records to target without using rank

SOLUTION:

Source: EMP Table

Target : EMP_BKP

* Same structure as off EMP.

Step 1: Drag the source to mapping and connect it to sorter transformation.

Step 2: Arrange the salary in descending order in sorter as follows and send the record to expression.

Step 3: Now create a Expression Transformation and connect it from sorter.Create a port as "NEXTVAL" with integer data type in Expression Transformation. Also Create a Sequence Transformation, start with 1 and connect nextval port to Expression's Nextval port shown below.

Step 4: Connect the expression transformation to a filter or router. In the property set the condition as follows..

Informatica Scenario: 2

Extracting every nth row

Scenario: How to load every nth row from a Flat file/ relational DB to the target? Suppose n=3, then in above condition the row numbered 3,6,9,12,....so on, This example takes every 3 row to target table.

Solution:

Step 1:
Connect an expression transformation after source qualifier.

Add the next value port of sequence generator to expression transformation.

Step 2:

In expression create a new port (validate) and write the expression like in the picture below.

Step 3:

Connect a filter transformation to expression and write the condition in property like in the picture below.

Step 4:

Finally connect to target.

Step 5: Finally Connect to Target.

Informatca Scenario: 3

HOW TO CONVERT ROWS INTO COLUMNS

SOLUTION:

SOURCE:

ID	Month	Sales
1	Jan	100
1	Feb	120
1	March	135
2	Jan	110
2	Feb	130
2	March	120

TARGET:

ID	Jan	Feb	March
1	100	120	135
2	110	130	120

Use the Aggregator group by ID and use First function
FIRST(AMOUNT, MONTH='JAN')
FIRST(AMOUNT, MONTH='FEB')
FIRST(AMOUNT, MONTH='MAR')

Informatica Scenario: 4

SPLIT NON-KEY COLUMNS TO SEPARATE TABLES WITH KEY COLUMNS IN BOTH

SOLUTION:

Split the non-key columns to separate tables with key column in both / How to split the data of source table column-wise with respect to primary key. See the source and target tables below.

SOURCE:

PROD_ID	PROD_NAME	PROD_CATEGORY
13	5MP Telephoto Digital Camera	CAMERAS
14	17" LCD w/built-in HDTV Tuner	MONITORS
15	Envoy 256MB - 40GB	DESKTOP PCS
16	Laptop carrying case	LAPTOP ACCESSORIES

INSERT INTO SRC_PRODUCT VALUES (13, '5MP Telephoto Digital Camera','CAMERAS');

INSERT INTO SRC_PRODUCT VALUES (14, '17" LCD w/built-in HDTV Tuner','MONITORS');

INSERT INTO SRC_PRODUCT VALUES (15, 'Envoy 256MB - 40GB','DESKTOP PCS');

INSERT INTO SRC_PRODUCT VALUES (16, 'Laptop carrying case','LAPTOP ACCESSORIES');

TARGET:

PROD_ID	PROD_NAME
13	5MP Telephoto Digital Camera
14	17" LCD w/built-in HDTV Tuner
15	Envoy 256MB - 40GB
16	Laptop carrying case

PROD_ID	PROD_CATEGORY
13	CAMERAS
14	MONITORS
15	DESKTOP PCS
16	LAPTOP ACCESSORIES

Step 1:

Source qualifier: get the source table to the mapping area. See image below.

Step 2:

Drag all the port from (from the previous step) to the Aggregator transformation and group by the key column. Since we have to split the columns to two different tables with the key column in each, so we are going use two expression transformation, each will take the key column and one non-key column. Connect aggregator transformation with each of the expression transformation as follows.