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:
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
