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
regarding last transaction and current transaction
Moderators: chulett, rschirm, roy
Re: redgarding last transaction and current transaction
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
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
-
- Participant
- Posts: 91
- Joined: Wed Apr 20, 2005 7:59 pm
- Location: U.S.
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.
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.
Re: regarding last transaction and current transaction
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
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
-
- Participant
- Posts: 91
- Joined: Wed Apr 20, 2005 7:59 pm
- Location: U.S.