how to get previous row value in current row
Moderators: chulett, rschirm, roy
how to get previous row value in current row
source
id,sal
10,1000
20,2000
30,3000
40,5000
target
id,sal,pre_row_sal
10,1000,null
20,2000,1000
30,3000,2000
40,5000,3000
id,sal
10,1000
20,2000
30,3000
40,5000
target
id,sal,pre_row_sal
10,1000,null
20,2000,1000
30,3000,2000
40,5000,3000
jhansi
Re: how to get previous row value in current row
you can implement the logic using stage variables in Transformer stage
---------
Pons
---------
Pons
Re: how to get previous row value in current row
you can implement the logic using stage variables in Transformer stage
---------
Pons
---------
Pons
Re: how to get previous row value in current row
can u explain me the logic? i did not get proper output with my logic
TPons wrote:you can implement the logic using stage variables in Transformer stage
---------
Pons
jhansi
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Did you search DSXchange? The technique of using stage variables to remember a value from the previous row has been explained on a number of occasions.
In short, though, the technique relies on the fact that stage variables are executed in the order (top down) in which they appear in the stage variables grid. So, for example, when the first stage variable is being executed for the current row, the second and subsequent stage variables still contain their value from the previous row.
Stage variables are initialized before the first row (per node) is processed, and not re-initialized after that.
In short, though, the technique relies on the fact that stage variables are executed in the order (top down) in which they appear in the stage variables grid. So, for example, when the first stage variable is being executed for the current row, the second and subsequent stage variables still contain their value from the previous row.
Stage variables are initialized before the first row (per node) is processed, and not re-initialized after that.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
i got solution for this.but how to get next row value to current row?
ray.wurlod wrote:Did you search DSXchange? The technique of using stage variables to remember a value from the previous row has been explained on a number of occasions.
...
jhansi
This is technically impossible, because you can't use a value in a row that DataStage has not read at the time you want to use it.jhansi wrote:i got solution for this.but how to get next row value to current row?
For many cases a simple solution may be to reverse the sort-order of your input and use the same technique as described above, though.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
actually my requirement is
id,sal,next_row_sal
10,1000,2000
20,2000,3000
30,3000,5000
40,5000,null
id,sal,next_row_sal
10,1000,2000
20,2000,3000
30,3000,5000
40,5000,null
BI-RMA wrote:This is technically impossible, because you can't use a value in a row that DataStage has not read at the time you want to use it.jhansi wrote:i got solution for this.but how to get next row value to current row?
For many cases a simple solution may be to reverse the sort-order of your input and use the same technique as described above, though.
jhansi
Run your stream through two outputs of a transformer. Add a Dummy-column to both streams. Provide @Outrownum as the Derivation for one and @Outrownum -1 as the derivation to the other. Left Join the resulting streams with the Dummy column as Join-key. Rename the sal-column on the left-joined stream next_row_sal.
Check the result.
Check the result.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
It does not matter where your data comes from or is written to.
You send the data from one source downstream through two output-links of the same transformer and left join both output-streams to each other.
DS --> TFM ===> Join --> DS
You send the data from one source downstream through two output-links of the same transformer and left join both output-streams to each other.
DS --> TFM ===> Join --> DS
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: