How to find the number of success , rejected and bad records in the same mapping.

How to find the number of success , rejected and bad records in the same mapping.

In this Mapping we will see how to find the number of success , rejected and bad records in one mapping. Source file is a flat file which is in .csv format . Click here to download the source file.The table appears like as shown below.. EMPNO NAME HIREDATE SEX 100 RAJ 21-APR M 101 JOHN 21-APR-08 M 102 MAON 08-APR M 103 22-APR-08 M 105 SANTA 22-APR-08 F 104 SMITHA 22-APR-08 F 106 M In the above table it shows few values are missing in the table .ANd also the date format of few records are improper.This must be considered as invlaid records and should be loaded into Bad_records table ( target table which is relational). Other than 2 , 3 , 5, 6 records ,remaining all are invalid records because of NULL values or improper DATE format or both . INVALID & VALID RECORDS :: First we seperate this data using Expression transformation.Which is used to flag the row for 1 or 0 .The condition as follows .. IIF(NOT IS_DATE(HIREDATE,'DD-MON-YY') OR ISNULL(EMPNO) OR ISNULL(NAME) OR ISNULL(HIREDATE) OR ISNULL(SEX) ,1,0) FLAG =1 is considered as invalid data and FLAG =0 is considered as valid data .This data will be routed into next transformation using router transformation .Here we added two user groups one as FLAG=1 for invalid data and the other as FLAG=0 for valid data. FLAG=1 data is forwarded to the expression transformation .Here we take one variable port and trwo ouput ports .One for increament purpose and the other for flag the row ... INVALID RECORDS INCREAMENT :: PORT EDIT EXPRESSION COUNT_INVALID V_PORT ( output port ) V_PORT V_PORT+1 ( variable) INVALID DATE :: PORT EDIT EXPRESSION INVALID_DATE IIF( IS_DATE(O_HIREDATE,'DD-MON-YY'), O_HIREDATE, 'INVALID DATE') This data will be moved to the BAD_RECORDS table.Look at the below table:: EMPNO NAME HIREDATE SEX COUNT 100 RAJ INVALID DATE M 1 102 MAON INVALID DATE M 2 103 NULL  22-APR-08 M 3 106  NULL  NULL M 4 VALID RECORDS :: In this we will have the valid records.But here we dont want the Employee ,who is 'F' (Female).So our goal is to load MALE employee info., into the SUCCESS_RECORDS target table. For this we need to use a Router transformation and declare the user group as follows \ IIF( sex='M',TRUE,FALSE) And the defined group will capture teh rejected records which are nothing but employee who is FEMALE . This data passed to the REUSABLE Expressiona transformation.Where the Increamental logic is applied to get the count value for the the no., of success and rejected records which are passing it.And loaded into the target table. Look at the below tables ::: SUCCESS_RECORDS:: EMPNO NAME HIREDATE SEX COUNT 101 JOHN 22-APR-08 M 1 REJECTED_RECORDS:: EMPNO NAME HIREDATE SEX COUNT 105 SANTA 22-APR-2008 F 1 106 SMITHA 22-APR-2008 F 2