Thread: Getting the previous value of a column

1. Member
Join Date
Aug 2011
Posts
77

Getting the previous value of a column

HI,
I want to do an operation like

B = If([A]=0;[C];[B])

where the B inside the operation should give the previous value of B.

For example,

initial value of B is zero..

C A B
2 1 0
4 0 4
5 3 4
7 0 7

I need an output like this.

How can i do this???

2. Senior Member
Join Date
Jul 2009
Posts
476
The "Analytic Query" step, in the "Statistics" group, may do what you need. It has LEAD and LAG operators that allow you to access preceding or trailing rows in your data stream.

3. Senior Member
Join Date
Nov 2008
Posts
777
I think it can also be done in Javascript although I'm a little unsure of what you mean when you say "the B inside the operation".

Code:
```var previous_B;
var current_B;

if (previous_B == null) {
previous_B = 0;
}

current_B = B;

if (A == 0) {
B = C;
} else {
B = previous_B;
}

previous_B = current_B;```

4. Chief Data Integration
Join Date
Nov 1999
Posts
9,729
The JavaScript would work comparably ONLY if you don't consider data groups.
Setting previous_B to 0 is also a bit dodgy since returning null would be more appropriate.

5. Senior Member
Join Date
Nov 2008
Posts
777
Agreed. As I said, I was a little unsure of meaning of some of the stated logic. I made the "previous_B = 0" leap of faith initializer based on the statement "initial value of B is zero.." even though that is actually part of the example. I also see now that if subsequent rows had a null B they would be handled the same way. It's hard to know if that was the intent.

6. Member
Join Date
Aug 2011
Posts
77
Hi,

Is there any way to get in a formula step???

The value of B is getting generated dynamically.. If we can declare a B as a variable, it is very easy to get this done..
But in PDI do we have an option to create variables????

7. Senior Member
Join Date
Apr 2008
Posts
1,771
Yes, it's possible to create variables.
Check "Set Variables" step and "Get Variables" step.

If you do a search on the forum you should find examples and even other method for setting variables.

Mick

8. Member
Join Date
Aug 2011
Posts
77
Hi,
Can we use the variables which are created in the same transformation ???

IT IS NOT POSSIBLE TO SET AND USE A VARIABLE IN THE SAME TRANSFORMATION. This is because all steps run in parallel.

9. Senior Member
Join Date
Nov 2008
Posts
777
In PDI, think of a Variable as an Environment Variable. The value has to be set before your transformation runs and can't be modified while it is running. I don't think that's what you want.

So...what do you really want? Why are you wanting to create a variable when you are only re-calculating B? You already have B as a "field" in each input row.

Also, you can't access fields in previous rows directly in the formula step. Very few steps allow you to do that. The Javascript step will (as I showed you earlier). The Analytic Query step also does (as stated by Matt) but I am less familiar with that step.

10. Senior Member
Join Date
Apr 2007
Posts
2,010
put simply, the analytic query step allows you to put the previous value of B into the stream as another column!

So you can have 2 columns

B
PreviousB

And then you can use your formula step i suspect.

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•