There can be many scenarios where you need the previous record's data to do some calculation, while the current record is processed in an informatica mapping. For example you need the previous days closing balance to calculate the loss or gain while processing the current day's data. Here in this tutorial we will discuss some techniques to get the previous record values while we are processing current record in a mapping.
Here let's take the sample data, which we get from the Stock Exchange for the Daily closing price of the socks. In our mapping we need to build a logic to find the daily loss or gain on the stock and for this calculation we need the data from the previous day when we are processing the current day's data.
Lets see how we are going to do this in the mapping. Start the mapping creation and add an EXPRESSION transformation.
Add the Ports in the Expression.
Note : If your data source is not in Sorted Order, add a SORTER Transformation to get the data sorted.
- V_GAIN_LOSS as Variable Port.
- V_PRIOR_CLOSING_PRICE as Variable Port.
- GAIN_LOSS as Output Port.
Note : Add the ports in the same order in the expression
- V_GAIN_LOSS :- CLOSING_PRICE - V_PRIOR_CLOSING_PRICE
- V_PRIOR_CLOSING_PRICE :- CLOSING_PRICE
- GAIN_LOSS :- V_GAIN_LOSS
Now map the columns to the Target and we have the mapping as we have it in the below image.
Key points behind this Mapping Logic
Now we have the mapping ready, lets see what is the key logic behind the mapping. This logic is build based on the way the ports are evaluated in the EXPRESSION transformation.
In an EXPRESSION Transformation.
- Ports are evaluated in the order
- Ports are evaluated from Top to Button
Try it Yourself.
The mapping we build right now can handle only file with single company stock prices, this logic breaks with multiple. Modify this mapping to handle files with multiple company stock price.
Download.Click Here to download the Source Code, Sample Data and other relevant file.
Hope this tutorial was helpful. Please let us know any questions or comments you may have. We are more than happy to help you.