Tuesday, September 11, 2012

ETL: How to select one row for a set of columns, in Informatica ?

There was a Informatica design problem i had to solve, as part of the solution i had find a way to select one row for a set of columns.
Here's the scenario

Col1 Col2 Col3 Col4
A      1       a1     a11
A      1       a1     a11
A      2       a1     a11
A      2       a1     a11
B      2       b2     b22

I have to select one record for a set of values in Col1 and Col2.

Solution - Use an Aggregator transformation, Select Col1 and Col2 as group by column. An aggregator transformation will select one record for the selected group by columns, the last record by design.

The output of the Aggregator transformation will be like this.


Col1 Col2 Col3 Col4
A      1       a1     a11
A      2       a1     a11
B      2       b2     b22

Note - We cannot achieve this by using a group by function in DB, thus making this feature of Aggregator unique.

No comments:

Post a Comment