INFORMATICA How to DENSE_RANK

EXAMPLE:

We want to make RANK object which is  group by COL1,COL2  and ORDER BY COL4 ASC

STEP1:

CREATE TABLE RANKER_TMP   -- SOURCE_TABLE

(

COL1  NUMBER,

COL2  VARCHAR2(100 CHAR) ,

COL3  VARCHAR2( 100 CHAR),

COL4 NUMBER

)

CREATE TABLE RANKER_TMP2   --- TARGET_TABLE

(

RANKER NUMBER,

COL1  NUMBER,

COL2  VARCHAR2(100 CHAR) ,

COL3  VARCHAR2( 100 CHAR),

COL4 NUMBER

)

STEP2:

Insert into RANKER_TMP

   (COL1, COL2, COL3, COL4)

Values

   (1, 'V', 'yes', 3);

Insert into RANKER_TMP

   (COL1, COL2, COL3, COL4)

Values

   (1, 'V', 'no', 3);

Insert into RANKER_TMP

   (COL1, COL2, COL3, COL4)

Values

   (1, 'V', 'YES', 2);

Insert into RANKER_TMP

   (COL1, COL2, COL3, COL4)

Values

   (2, 'b', 'yes', 1);

Insert into RANKER_TMP

   (COL1, COL2, COL3, COL4)

Values

   (2, 'b', 'no', 6);

Insert into RANKER_TMP

   (COL1, COL2, COL3, COL4)

Values

   (2, 'a', 'no', 2);

COMMIT;

STEP3:

In order to make DENSE_RANK we gonna use SORT  and EXPRESSION objects together.Mapping should be look like this

*SORTER  has to be SORTER BY COL1 ,COL2 , COL4  ASC ORDER

*In Informatica , EXPRESSION object  calculates values  from TOP to BOTTOM  

( it first calculates INPUTs then VARIABLEs and last  OUTPUTs. As a result , we can catch previous values by  defining VARIABLE which map to COLUMN values )

EXPRESSION PORTS should be look like this :

* VAL_DIFF used for calculating RANK values. Formula should be look like this:

*Result :