Tutorial corner

Informatica,ETL,oracle,sql/plsql

Example of Rank transformation in informatica

Spread the love

Earlier we talked about Rank transformation in Informatica , steps to create it . We also discussed the business scenario of Rank Transformation in Informatica. In below article we will go through a working  Example of Rank transformation in informatica mapping .

Example of Rank transformation in informatica

Create a mapping to load the target table with top 2 income  (employees) in each department with the help of  Rank Transformation.

Source Data:

EMP Table Data:

EMPNO ENAME JOB MGR SAL COMM DEPT

1

JOHNSON ADMIN

6

18000

4

2

HARDING MANAGER

9

52000

300

3

3

TAFT SALES I

2

25000

500

3

4

HOOVER SALES I

2

27000

3

5

LINCOLN TECH

6

22500

1400

4

6

GARFIELD MANAGER

9

54000

4

7

POLK TECH

6

25000

4

8

GRANT ENGINEER

10

32000

2

9

JACKSON CEO

75000

4

10

FILLMORE MANAGER

9

56000

2

11

ADAMS ENGINEER

10

34000

2

12

WASHINGTON ADMIN

6

18000

4

13

MONROE ENGINEER

10

30000

2

14

ROOSEVELT CPA

9

35000

1

Target Data:

EMP_TAR  Data:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPT

14

ROOSEVELT CPA

9

10/12/1995

35000

1

10

FILLMORE MANAGER

9

8/9/1994

56000

2

11

ADAMS ENGINEER

10

3/15/1996

34000

2

2

HARDING MANAGER

9

2/2/1998

52000

300

3

4

HOOVER SALES I

2

4/2/1990

27000

3

9

JACKSON CEO

1/1/1990

75000

4

Must see split column to row in informatica

Solution:

Below are the steps to create full mapping

  • Create a new mapping m_Rank_example by Go to toolbar -> click mapping-> Create
  • Drag Source (EMP) and Target (EMP_TAR) to the mapping.
  • Add Rank Transformation by “Go to Toolbar->click Transformation -> Create. Select the Rank transformation”
  • Enter the  name “rnk_Sal_by_dept” and click Done.
  • Drag input port from EMPNO,ENAME, SAL, DEPTNO from SQ_EMP (Source Qualifier ) to rnk_Sal_by_dept
  • Check the “Group By” option for the DEPTNO port.
  • Also Check the “Rank” option for SALport
  • Rank Transformation Part 1

    Rank Transformation Part 1

  • In properties Tab, select “Top” in Top/Bottom tab and “2” as Number of Ranks.
Rank Transformation Part 2

Rank Transformation Part 2

  • Now link all required port from rnk_Sal_by_deptto EMP_TAR Target Definition,
  • Click on Mapping ( from tool bar) -> then Validate ( to validate the mapping)
  • Now save the mapping ( by clicking on Repository-> Save or you can also press Ctrl+S)
  • Generate the workflow and run it

Must see Informatica Normalization Transformation

Over all Mapping:

Full Mapping of Rank Transformation

Example of Rank transformation in informatica

Full Example of Rank Transformation in Informatica mapping

Here we have gone through a detailed Example of Rank transformation in informatica . For more informatica tutorial , please keep on visiting us .

The Author

Alisha Lamba

Hello Friends , I am Alisha Lamba .I love to write article on latest technologies like Informatica , ETL , data warehouse , SQL-PL SQL
Copyright 2015 - Tutorial Corner Frontier Theme