Sunday, 4 September 2011

How to concatenate values from multiple rows in Informatica

In Informatica , many times we face scenario where we have to concatenate values from two or more rows into a single row.

Consider a source which contains the below records:
ID Name
1 A
2 R
3 B
1 D
2 E
2 O

Suppose we want output like this:
ID Name
1 D,A
2 O,E,R
3 B

Above scenario can be implemented in Informatica as depicted below:

1) Create source definition which contains the columns named ID and NAME
2) After source qualifier place a sorter and sort the data in ascending order . Here sort key will be ID and sort order will be ascending order.

3) After sorter put an expression transformation . In expression crate following fields in addition to the fields coming from sorter
v_NAME(variable) IIF(ID=v_ID,NAME||','||v_NAME,NAME)
out_NAME(output field)=v_NAME

4) After expression place an aggregator . In aggregator group by will be on ID.

5) Then take the ID and NAME field to the target.

Saturday, 3 September 2011

SAS for Begineers

This is a nice ppt to get start with SAS.....

SAS 101 The Beginner�s Guide to Beginning in SAS