Find the rank of the employee within department based on salary

My source is

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	12/17/1980	800		20
7499	ALLEN	SALESMAN	7698	2/20/1981	1600	300	30
7521	WARD	SALESMAN	7698	2/22/1981	1250	500	30
7566	JONES	MANAGER	7839	4/2/1981	2975		20
7654	MARTIN	SALESMAN	7698	9/28/1981	1250	1400	30
7698	BLAKE	MANAGER	7839	5/1/1981	2850		30
7782	CLARK	MANAGER	7839	6/9/1981	2450		10
7788	SCOTT	ANALYST	7566	12/9/1982	3000		20
7839	KING	PRESIDENT		11/17/1981	5000		10
7844	TURNER	SALESMAN	7698	9/8/1981	1500		30
7876	ADAMS	CLERK	7788	1/12/1983	1100		20
7900	JAMES	CLERK	7698	12/3/1981	950		30
7902	FORD	ANALYST	7566	12/3/1981	3000		20
7934	MILLER	CLERK	7782	1/23/1982	1300		10

My target would be

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	DEPTNO	rank
7839	KING	PRESIDENT	-	29907	5000	10	1
7782	CLARK	MANAGER	7839	29746	2450	10	2
7934	MILLER	CLERK	7782	29974	1300	10	3
7788	SCOTT	ANALYST	7566	30294	3000	20	1
7902	FORD	ANALYST	7566	29923	3000	20	1
7566	JONES	MANAGER	7839	29678	2975	20	2
7876	ADAMS	CLERK	7788	30328	1100	20	3
7369	SMITH	CLERK	7902	29572	800	20	4
7698	BLAKE	MANAGER	7839	29707	2850	30	1
7499	ALLEN	SALESMAN	7698	29637	1600	30	2
7844	TURNER	SALESMAN	7698	29837	1500	30	3
7521	WARD	SALESMAN	7698	29639	1250	30	4
7654	MARTIN	SALESMAN	7698	29857	1250	30	4
7900	JAMES	CLERK	7698	29923	950	30	5

Solution:

    1. Bring the source into the mapping designer.
    2. Take a sorter transformation. ( If you do sorting in the source qualifier will be the best one).
    3. Apply sorting logic as SELECT * FROM EMP ORDER BY DEPTNO ASC, SAL DESC
   

Note: If you keep DEPTNO port after the SAL port then the sql query will become SELECT * FROM EMP ORDER BY SAL DESC, DEPTNO ASC. That will give wrong result. So make sure you keep the DEPTNO port before the SAL port.
 
    4. Take an expression transformation and create four ports as
        rank_v = iif(DEPTNO = prev_deptno_v,iif(SAL != prev_sal_v,rank_v + 1, rank_v) , 1 )
        prev_deptno_v = DEPTNO
        prev_sal_v  = SAL
        rank_o = rank_v
     

      5. Pass the ports into the target.

      That's all and whole final mapping is