DSXchange

Hi,
I have a requirement like from source I am getting valid from date for which I have to calculate valid to date.
The data is actually about currency exchange rate. I will data in a file and it is a monthly file, it will have four columns which are given below.

from_currency
to_currency
valid_from
exchange_rate

The problem I have is currency rate will change many times in a month and there will be multiple records for each currency with different valid from dates.
When there are multiple exchange rates in a month for same currency, then valid to date should be next validdate-1
Below is the sample data I am getting and expected result.

Source Data

CURR_FROM|CURR_TO|VALIDFROM|EXCHNG_RATE
CNH|USD|1-Jan-12|0.11
CNH|USD|5-Jan-12|0.8
CNH|USD|10-Jan-12|0.15
CNH|USD|15-Jan-12|0.14
CNH|USD|20-Jan-12|0.16


Expected Output

CURR_FROM|CURR_TO|VALIDFROM|VALIDTO|EXCHNG_RATE
CNH|USD|1-Jan-12|4-Jan-12|0.11
CNH|USD|5-Jan-12|9-Jan-12|0.8
CNH|USD|10-Jan-12|14-Jan-12|0.15
CNH|USD|15-Jan-12|19-Jan-12|0.14
CNH|USD|20-Jan-12|31-Dec-99|0.16


Please help me in solving this scenario.
Many thanks in advance

Stage variables can be used to store values from the previous record, so in this case you will want to sort your data by currency and descending from_date.
The first record per currency will get a valid_to date of your high-date, then store that record's from_date in a stage variable and use that value, minus 1 day, for the next record's valid_to date.