Scenario 13: How to Convert Row to Column

In this section we have several scenario based upon source data and expected output. We will explore it one by one.

Scenario 13: How to achieve target below...

(Source)
BRAND_ID,PRODUCT,PRICE
100,Cellphone,10000
100,Television,15000
100,Fridge,9000
101,Cellphone,8000
101,Television,12000
102,Cellphone,9000

(Target)
BRAND_ID, PRODUCT, TOTAL_PRICE
100,Cellphone,Television,Fridge,34000
101,Cellphone,Television,20000
102,Cellphone,9000

Analysis/Theory
After analyzing our source and target we can say that we have to pass one record for each group after combining all product and adding price of all products as Total Price. Fine, so once again sorting, comparison and if current records matched with previous one then keep adding Product and Price. The challenge is here when to pass the records to target ah!!! simple answer when old record and current record does not match then pass the record... No,No,No logically it's correct but practically we won't be getting expected result :-(
Want to feel HEAT? Sit on the hot seat.

Practically

Step 1:
Drag and Drop your source and target to mapping designer.

Step 2:
Sorter
Sort the data based upon BRAND_ID

Step 3:
Expression
Create Eleven ports
BRAND_COMPARE (Variable Port) = IIF(OLD_BRAND=BRAND_ID, 1, 0)

OLD_BRAND (Variable Port) = BRAND_ID

PRODUCT_LIST (Variable Port) = IIF(BRAND_COMPARE=1, O_PRODUCT_LIST||','||PRODUCT, PRODUCT)

O_PRODUCT_LIST (Variable Port) = PRODUCT_LIST

ADD_PRICE (Variable Port) = IIF(BRAND_COMPARE=1, (O_PRICE_ADD + PRICE), PRICE)

O_PRICE_ADD (Variable Port) = ADD_PRICE
NEW_ID (Variable Port) = OLD_ID+1
OLD_ID (Variable Port) = NEW_ID
ID (Output Port) = NEW_ID
OUT_PRODUCT_LIST (Output Port) = PRODUCT_LIST
OUT_PRICE (Output Port) = ADD_PRICE

Step 4:
Rank
Create Rank Transformation and drag and drop BRAND_ID, ID, OUT_PRODUCT_LIST, OUT_PRICE to it from expression.
Set properties in Ports Tab.
Select ID as Rank Port (R) should be checked.
Select BRAND_ID as Group
Set Top/Bottom-->TOP in Properties Tab.

Clarification: We are using Rank Transformation to get the last record of particular Brand_ID, as we are using variable ports to combine product and price so, it would be overwritten when new Brand_ID comes. If any one has other approach for how to pass the combine product list and total price to next target. Please let us know through comment section and no need to say No Verbal answer... Post it in Practical way.. I have spent approx 2 hours by applying different approach.. and at last I finalize to use Rank. In the last I am not a genius, I am a beginner and still learning Informatica, Spread your knowledge if you know, it will help you a lot in your growth.   Once again too much lecture.. It seems, I am too much talkative, Sorry... though it's not true. :-)

Step 5
Connect BRAND_ID, OUT_PRODUCT_LIST, OUT_PRICE column to target.

BINGO!!!

Feel free to post your doubt in comment section.

Due to limitation, I can't attach exported .xml file which you can Import in your repository and see or analyse. (Feel free to mail me if you need .xml file with Scenario number and Question.)

If you like the approach and solution feel free to share with others.

Thanks.
hpandey.harsh@gmail.com