Informatica Developer

There is a source table containing 2 columns Col1 and Col2 with data as follows:


Col1 Col2

a l

b p

a m

a n

b q

x y


Design a mapping to load a target table with following values from the above mentioned source:

Col1 Col2

a l,m,n

b p,q

x y



Solution:



Use a sorter transformation after the source qualifier to sort the values with col1 as key. Build an expression transformation with following ports(order of ports should also be the same):



1. Col1_prev : It will be a variable type port. Expression should contain a variable e.g val

2. Col1 : It will be Input/Output port from Sorter transformation

3. Col2 : It will be input port from sorter transformation

4. val : It will be a variable type port. Expression should contain Col1

5. Concatenated_value: It will be a variable type port. Expression should be decode(Col1,Col1_prev,Concatenated_value

','

Col2,Col1)

6. Concatenated_Final : It will be an outpur port conating the value of Concatenated_value



After expression, build a Aggregator Transformation. Bring ports Col1 and Concatenated_Final into aggregator. Group by Col1. Don't give any expression. This effectively will return the last row from each group.


Connect the ports Col1 and Concatenated_Final from aggregator to the target table.