How to handle this kind of situation

[bharathappriyan]

Hi all,
I have requirement in DataStage Server job.
The transaction done by a customer for a particular month is summarized and stored in a table for last 24 months.
The layout of record is like below:
AC_NO PERIOD RETLQTY RETLAMT RETURNQTY RETURNAMT ATMQTY ATMAMT CADVQTY CADVAMT
100 2005-10 0 100 0 0 0 0 0 0
100 2005-09 10 0 0 0 0 0 0
100 2005-08 0 0 0 0 0 0 10 0
100 2005-07 0 0 0 0 0 0 0 0
100 2005-06 0 0 0 0 0 0 0 10
100 2005-05 0 0 20 0 0 0 0 0
100 2005-04 0 0 0 0 0 0 0 0
100 2005-03 0 0 0 0 0 0 0 0
100 2005-02 0 0 0 20 0 0 0 0
100 2005-01 0 0 0 0 20 0 0
100 2004-12 0 0 0 0 0 20 0 0
100 2004-11 0 0 0 0 0 0 0 0
100 2004-10 0 0 0 0 0 0 0 0
100 2004-09 0 0 0 0 0 0 0 20
100 2004-08 0 0 0 0 0 0 0 0
100 2004-07 0 0 0 0 0 0 0 0
100 2004-06 0 0 0 0 0 0 0 0
100 2004-05 0 0 0 0 0 0 0 0
100 2004-04 0 0 0 0 0 0 0 0
100 2004-03 0 0 0 0 0 0 0 0
100 2004-02 0 0 0 0 0 0 0 0
100 2004-01 0 0 0 0 0 0 0 0
100 2003-12 0 0 0 0 0 0 0 0
100 2003-11 0 0 0 0 0 0 0 0

first column is account no. Period is the column, shows the month for the transaction is summarized.
My requirement is to set flags for the fields RETLQTY,RETLAMT,RETURNQTY,RETURNAMT,ATMQTY,ATMAMT,CADVQTY and CADVAMT in another table.
For example,
for the account no 100 i have 24 months data.
if i run the job on Nov, 1st then i need take the previous months detail.i.e i need to take the record 2005-10 first. then i need
to check the field RETLQTY. if it has any value, i need to set flag as 01. if it not 0, then i need to go for previous month.i.e
2005-09. then i have check whether there is any value other than 0. if it is zero, then i need to go for previous month record. this is the way
i need to populte other fields also.

The output record should be as below:
AC_NO RETURNQTY_SPND_IN RETURNAMT_SPND_IN ATMQTY_SPND_IN ATMAMT_SPND_IN CADVQTY_SPND_IN CADVAMT_SPND_IN
100 6 9 ytd 12 3 PD

how do we store values of records temprorily?

please let me know if the requirement is not clear.

could any one help me out to resolve this situation?

Thanks in advance,
selva
[/list]

[Bala R]

Hi,
You could sort the data in descending order of period and then use stage variables to increment the RETLQTY.