Scenarios

Count the number of duplicate records in informatica

Consider a Source flat file
EID,ENAME,SAL
10,John,10000
20,David,20000
30,Smith,30000
10,John,10000
The target file will be like below
EID,ENAME,SAL,COUNT
10,John,10000,2
20,David,20000,1
30,Smith,30000,1
Solution:
  • Create a source flat file with the above source structure.
  • Create a target file in the relational table with the above target table structure.  
  • Both source and target files files can be drag in to the mapping designer.
  • Create an expression transformation, connect all ports from source qualifier to expression.
  • Add other output port to expression with O_ENAME
  • O_ENAME=ENAME
  • Create aggregator transformation, connect all ports from expression to aggregator.
  • In the aggregator, groupby on EMPID column
  • Write a expression on O_ENAME
  • O_ENAME=count(ENAME)
  • Connect all ports from aggregator target
  • connect O_ENAME to COUNT in target  


Powered by Blogger.