Ways to Sort and aggregate data in Informatica

Sorting  and aggregating data in  Informatica

Sorting In Informatica:

In the Source qualifier of Informatica mapping you can sort the data by manually overiding the SQL and adding order by clause to the SQL. Also, group by clause can be added to the source qualifier.

Select col1, col2, col3 from table1 order by col1, col2


The other way to sort data in the source qualifier of Informatica mapping is to use the Number of sorted ports field in the properties tab of the source qualifier (as shown in screenshot). You can set it to any number of ports you want to sort it on and it will sort the data based on the order the ports are in the source qualifier. If you set the number of sorted ports to 2, then it going to sort in ascending order of port1 and port2 which is eqvilent to using order 1,2 clause in the sql.

If you want to sort data in the mapping pipeline, then you can use the sorter transformation in Informatica. The columns to be sorted can be added to the sorted transformation in the informatica mapping and you can then sort those ports in ascending or descending order as shown in screen shot.

If you want the lookups to be sorted, then you can overide the lookup SQL and add order by clause to the SQL.

Aggregation in Informatica:

Similar to sorting, aggregation can also be done in the source qualifier by overiding the SQL and adding the group by clause. Preferable to do this way when you want the aggregation or sorting work to the database.

Select col1, col2, count(col3) from table1 group by col1, col2 order by col1, col2

Aggergation can be done in the mapping pipeline using the aggregator transformation. The aggregator transformation of Informatica shown in screen shot .