Aggregation without using aggregator transformation in informatica
Here the scenario is to find the sum of salary department wise.
My source is
|
Deptno |
Sal |
|
20 |
800 |
|
30 |
1600 |
|
30 |
1250 |
|
20 |
2975 |
|
30 |
1250 |
|
30 |
2850 |
|
10 |
2450 |
|
20 |
3000 |
|
10 |
5000 |
|
30 |
1500 |
|
20 |
1100 |
|
30 |
950 |
|
20 |
3000 |
|
10 |
1300 |
And my target will be
|
Deptno |
Sal |
|
10 |
8750 |
|
20 |
10875 |
|
30 |
9400 |
Solution:
Algorithm
1. Import source
2. If the source is relational database table apply sorting in the source qualifier. If the source is flat file take a sorter and pass ports from source into the sorter transformation and checked Deptno as key and select ascending in the Direction option. ( even though the source from relational database you can take a sorter transformation for sorting but the sorting in the source qualifier will increase performance).
3. Take an expression transformation and drag Deptno and Sal ports from the sorter or source qualifier which one you are using.
4. And do like
5. Take another sorter and apply
6. Again take a expression transformation and apply
7. Take a filter transformation and apply filter condition as FLAG_O =1 as
8. Pass the ports from filter into the target.
That’s it. The final mapping will look like this.
