Retain Values from a previously processed records for future calculations

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.

Note : If your data source is not in Sorted Order, add a SORTER Transformation to get the data sorted.

Add the Ports in the Expression.

• 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

Add the expression as follows.

• 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.

We are all done... except build and run the workflow.

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 

1. INPUT 
2. VARIABLE 
3. OUTPUT

• Ports are evaluated from Top to Button

Please look at the below chart to understand how the data is being processed through each ports for each row.

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.