Informatica mapping creation

Question

I need to create an Informatica mapping which can sort data and rank it. There are two tables in two different application so directly I cannot join them in SQL. Both has huge data so need to take care of the performance also. Senario could be like below-

There are two tables Department(DEPT) and Employee(EMP).They have 1(DEPT):M(EMP) relation. Department table has columns (Row_id, DeptNm, Created_date),Employee table has columns (Row_id, EMPName, Emp_num, Par_row_id (FK to DEPT.row_Id),Salary)

For a perticular Department sort data as per employee's decreasing salary and rank it. Data should come in text file as below -

DeptNm |EmpNm  |Salary|Rank
Finance|Vikram |200000|1
Finance|Uttaam |150000|2
Finance|Rajeev |100000|3
ITDPPT |Balaji |150000|1
ITDEPT |Harsha |120000|2
ITDEPT |Weeniji|100000|3

Answer 1

You can use a joiner transformation to join the department and employee tables. Then use a sorter and rank transformation to rank the rows.

Answer 2

Use the Joiner Transformation to join both the tables and then sort the data with respect to DeptNm column. Output will come like -
DeptNm |EmpNm |Salary
Finance |Vikram |200000
Finance |Uttaam |150000
Finance |Rajeev |100000
ITDPPT |Balaji |150000
ITDEPT |Harsha |120000
ITDEPT |Weeniji |100000

Add an EXPRESSION transformation to set a boundary flag for each DeptNm group. In the expression transformation add following ports.
- V_NEW_FLAG as Variable Port
- V_DEPT as Variable Port
- NEW_FLAG as Output Port
- V_COUNT_FLAG as Variable Port
- COUNT_FLAG as Output Port

And the following are the expressions for the ports.
- V_NEW_FLAG :- IIF(DeptNm = V_DEPT, 'N', 'Y')
- V_DEPT :- STATE
- NEW_FLAG :- V_NEW_FLAG
- V_COUNT_FLAG :- IIF(V_NEW_FLAG = 'Y', 1, V_COUNT_FLAG + 1)
- COUNT_FLAG :- V_COUNT_FLAG