regarding last transaction and current transaction

[ntr]

Hi,

I have input data like this

date , closingbal

12thmay,2000

13thmay,3000

14thmay,4000

17thmay,6000

20thmay,8000

and i need output like this

date closingbal,openingbal

12thmay,2000, 0

13thmay,3000, 2000

14thmay,4000, 3000

17thmay,6000,4000

20thmay,8000,6000

means my last transactions of closingbal current transactions opening bal


can please help me regarding this

iam using datastage 8.1, windows environment

Thanks

[sreewin7]

As per my knowledge it is not possible in datastage, we can do in reporting level and which reporitng tool is used in your environment and discuss with them try to slove the requirment.

Example:- In the reporting level we can use filter for the same column like below.
As of yesterday balance = 1
Today balance = 2
Output coloumn = 1+2.

Regards,
Sree

[chulett]

This can be done in DataStage using properly sequenced stage variables, all you are doing is holding on to the previous value of the closingbal for use in the openingbal of the next record.

[mobashshar]

Do this:
Create a rownum column as int so your input will look like this:

rownum, date , closingbal

1, 12thmay,2000

2, 13thmay,3000

3, 14thmay,4000

4, 17thmay,6000

5, 20thmay,8000

Now.. using Copy Stage, split the rows in Two stream as follows:
1. rownum and closingbal will go to Transformer.
2. rownum, date and closingbal will go to Merge.

In Transformer Add +1 to rownum and pass out the values (rownum+1 and openbal) to Merge.

In Merge, merge on Key=rownum and you will ge the result as follows:

12thmay,2000, 0
13thmay,3000, 2000
14thmay,4000, 3000
17thmay,6000,4000
20thmay,8000,6000

Hope this helps.

[ntr]

Thank you somuch i think it should work i will try iit now and i will come back with result

[TPons]

Use the below logics in stage variables

sv_Opening_Bal ---> if @INROWNUM=1 Then 0 Else sv_Prev_Clsoing_Bal
sv_Prev_Clsoing_Bal ---> closingbal

in colunm deivation, assign the stage variable sv_Opening_Bal to openingbal column.

date - date
closingbal - closingbal
openingbal - sv_Opening_Bal

---------------
Pons

[mobashshar]

Pons..

sv_Opening_Bal ---> if @INROWNUM=1 Then 0 Else sv_Prev_Clsoing_Bal
sv_Prev_Clsoing_Bal ---> closingbal

will not work in Parallel env unless you run your transformer in seq. mode.

[svga]

We don't need to use the @INROWNUM instead assign zero to initial value for stage variable.