Page 1 of 1
regarding last transaction and current transaction
Posted: Mon May 14, 2012 7:27 am
by 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
Re: redgarding last transaction and current transaction
Posted: Mon May 14, 2012 11:57 am
by 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
Posted: Mon May 14, 2012 1:09 pm
by 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.
Posted: Mon May 14, 2012 1:42 pm
by 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.
Posted: Mon May 14, 2012 11:20 pm
by ntr
Thank you somuch i think it should work i will try iit now and i will come back with result
Re: regarding last transaction and current transaction
Posted: Tue May 15, 2012 2:09 am
by 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
Posted: Tue May 15, 2012 8:54 am
by 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.
Posted: Tue May 15, 2012 1:12 pm
by svga
We don't need to use the @INROWNUM instead assign zero to initial value for stage variable.