Other way to do the mapping

[Sridharan]

Hello All,

With the following structures

Source
Item ID        Qty
100		10
101		40
102		50

Target
ITEM_ID	    QTY_SOLD	SALE_PRICE
....
...
...

Have another table which will contain the Price of each item for Sale Price calculation. One item will have many prices and we should take the maximum price date and take that price for calculation.

Price Table
ITEM_ID	PRICE_EFF_DATE	ITEM_PRICE
100	08/01/03	600
100	08/20/03	500
101	08/30/03	250
101	08/01/03	100
101	08/25/03	200
102	08/27/03	400

Now there are various ways of doing that and I've done it using some three methods(Use Aggregation with Joiner, Use Sorted Input etc). But I would like to know suggestions from peers.

Thanks
Sri

 

[blom0344]

There are many alternatives possible, but the first that comes to mind is the use of ranking.
Either you use this with an additional mapping for the price table OR you could create a database view that you will use for a lookup source.

The trick is to assign the rank '1' to the record (for each item) to the newest date for a price.

Consequently you use a lookup with 2 ingoing ports, one for the item, one for the ranking.

Well, this will be enough I guess for you to fill in the picture (If you are aware of the existence of OLAP functions like rank())
Otherwise I'll give some example code....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

Yes definitely thats a good suggestion. I did a similar thing using Rank Transformation since I don't want to touch the DB creating views. But any other method you feel will be good. Basically looking at handling this at Informatica Level.

Sri