Search code examples
informaticainformatica-powercenter

convert rows into Column informatica

could you please let me know how to transform the rows into Column in powerCenter? attached the Snapshot below.

enter image description here

Solution

  • All you need is an aggregator - group by Product and add two output ports to calculate the product value:

    • Current_value - MAX(IIF(end date = 1231999, Value, NULL))
    • Future_value - MAX(IIF(end date = 01010001, Value, NULL))

    UPDATE:

    PowerCenter aggregate functions take an optional filter_condition parameter, so you can also use the equivalent, more concise syntax:

    • Current_value - FIRST(Value, end date = 1231999)
    • Future_value - FIRST(Value, end date = 01010001)