Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Getting the previous value of a column

  1. #1

    Default 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. #2
    Join Date
    Jul 2009
    Posts
    476

    Default

    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. #3
    Join Date
    Nov 2008
    Posts
    777

    Default

    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;
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    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. #5
    Join Date
    Nov 2008
    Posts
    777

    Default

    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.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  6. #6

    Default

    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. #7
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    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. #8

    Default

    Hi,
    Can we use the variables which are created in the same transformation ???

    I read in http://wiki.pentaho.com/display/EAI/Set+Variable that

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

  9. #9
    Join Date
    Nov 2008
    Posts
    777

    Default

    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.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  10. #10
    Join Date
    Apr 2007
    Posts
    2,010

    Default

    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
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.