Search code examples
informaticainformatica-powercenterinformatica-cloud

How to convert several rows from a source flat file into a single column

How to convert several rows into a single column using Informatica Cloud or Power Center?

I've tried using aggregator transformation however is not working

enter image description here

What I need is like the example above.

Solution

  • It'll look like:

    SOURCE >> SOURCE QUALIFIER >> SORTER >> EXPRESSION >> AGGREGATOR >> TARGET

    In SORTER transformation set sorting key for NUM column.
    In EXPRESSION transformation after NUM and COD ports add four new ports and assign next expressions:

    v_COD (variable) = iif(NUM = v_prev_NUM, v_prev_COD||', '||COD, COD)
v_prev_COD (variable) = v_COD
out_COD (output) = v_prev_COD
v_prev_NUM (variable) = NUM

    Connect NUM and out_COD to AGGREGATOR transformation.
    In AGGREGATOR transformation set Group by key to NUM port and check Sorted Input property.
    Next is TARGET…
    That’s it!