(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
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.
Step 5a
Connect BRAND_ID, OUT_PRODUCT_LIST, OUT_PRICE column to target.
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
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.
Step 5a
Connect BRAND_ID, OUT_PRODUCT_LIST, OUT_PRICE column to target.
hai brother i can't understand the what is need of Dynamic look up cache :Associated Expression ? when we it use ? explain with one scenario
ReplyDeleteThanks in advance ....
when we vill use dynamic lookup cache means when ever we have source data with duplicate records and we need to perform update/insert means then we vil go with Dynamic lookup cache.when u enable that check box we will get one additional port called New Lookup Row .it will returns always 0 or 1,or 2 .......when it will returns these values means by comparing associate ports it will returns the values .here associate ports are used to capture the previues records information.and it will compare with current process record.
ReplyDelete