Powered By Blogger

Sunday, May 29, 2011

How to transpose data in Informatica

Suppose we are having input data coming as

Firstname1
Ph1
Address1

Firstname2
Ph2
Address2

Firstname3
Ph3
Address3

You want data in output like i.e. you want to convert 9 rows into 3 rows with similar group together

Firstname1 Ph1 Address1
Firstname2 Ph2 Address2
Firstname3 Ph3 Address3

You can create a mapping by following these steps
  1. Create a SQ with one input field Field1

  2. Add a expression transformation after SQ

  3. Link Field1 from SQ to expression Transformation

  4. Add a new op port name grp =substr(FIELD1,length(FIELD1),1)

  5. Add a aggregator transformation after Expression and drag Field1 and Grp field

  6. Have grp as group by fieldAdd three new port OP_FLD1=LAST(FIELD1,SUBSTR(FIELD1,1,1)='f')OP_FLD2=LAST(FIELD1,SUBSTR(FIELD1,1,1)='p')OP_FLD=LAST(FIELD1,SUBSTR(FIELD1,1,1)='a')
  7. Add OP_FLD1,OP_FLD2,OP_FLD3 to target

No comments: