How to pivot data using Informatica when you have variable amount of pivot rows?

Answer

 You should use something along the lines of this:

Source->Expression->Aggregator->Target

In the expression, add a variable port:

v_count expr: IIF(ISNULL(v_COUNT) OR v_COUNT=3, 1, v_COUNT + 1)

OR

v_count expr:  IIF(ADDR_ID=v_PREVIOUS_ADDR_ID, v_COUNT + 1, 1)

And 3 output ports:

o_addr1 expr: DECODE(TRUE, v_COUNT=1, ADDR_IN, NULL)
o_addr2 expr: DECODE(TRUE, v_COUNT=2, ADDR_IN, NULL)
o_addr3 expr: DECODE(TRUE, v_COUNT=3, ADDR_IN, NULL)

Then use the aggregator, group by ID and select always the Max, e.g.

agg_addr1: expr: MAX(O_ADDR1)
agg_addr2: expr: MAX(O_ADDR2)
agg_addr3: expr: MAX(O_ADDR3)

If you need more denormalized ports, add additional ports and set the initial state of the v_count variable accordingly.

All data warehouse Questions

Ask your interview questions on data-warehouse

Write Your comment or Questions if you want the answers on data-warehouse from data-warehouse Experts
Name* :
Email Id* :
Mob no* :
Question
Or
Comment* :
 





Disclimer: PCDS.CO.IN not responsible for any content, information, data or any feature of website. If you are using this website then its your own responsibility to understand the content of the website

--------- Tutorials ---