Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: JavaScript - Modifying Previous Row Value

  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Default JavaScript - Modifying Previous Row Value

    Hello,

    I have a question about JavaScript and modifying previous row values. The basic logic I am trying to achieve here is "If there are non-unique values in Field1, for which at least one Field2 value is X, then set all Field2 values to X".

    For example, a table is sorted on Field 1 like:

    +--------+--------+
    | Field1 | Field2 |
    +--------+--------+
    | A | 3 |
    | B | 2 |
    | B | 1 |
    +--------+--------+

    What I would like to do is find two consecutive values in Field1 (in this case, 'B' is consecutive). Then check if either has Field2 to be a certain value (in this case lets say '1'). This checking is the easy part based on tracking previous values, of which there are many examples on the forums.

    The hard part is then modifying the previous row. In the above example, both consecutive rows with Field1 = 'B' must be changed to have Field2 = '1'. This requires a modification of a previous row's value rather than just tracking... is this possible? My initial gut says no since it is streamed.

    Now if it is not possible to modify previous rows, I wonder if it is possible to view the following row's value. So instead of checking the current against the previous value, I could check the current against the next value, giving me the opportunity to put in logic for changing the current or next row as needed.

    Thanks!

    SD.

  2. #2

    Default possible algorithm

    Suggestion (using your example as illustration):

    1.Create a new field in the stream, field3.
    The value of this field will be 1 if field 2 is X and 0 otherwise. You could accomplish this with Javascript or a Filter Rows condition followed by a pair of 'Add Constant' steps (1 for the path with X and 0 for the path without X) followed by a rejoining of the stream.

    2. Next, sort by field 1 (either ascending or descending) and then by field 3 (descending). This will put all your field 1 groups together, with any X's at the top of each group (because they will have 1's in field 3 and other rows will have 0's).

    3. Next, use a Javascript step to 'copy down' any X's in field2 among the rows in each group.

    Code:
    var prevField1;
    
    var comparisonVariable; var c; var d; var str; var str2;
    if (prevField1 == null) // if first row of data stream {
    prevField1 = field1; comparisonVariable = field2; } else // we are into our stream and will now always have a previous row // with which to compare
    { // couldn't successfully match field1 and prevField1 without this str = field1; str2 = prevField1; c = str.charAt(0); d = str2.charAt(0);
    if (c != d) // if this row begins a new group
    { comparisonVariable = field2; // set comparisonVariable to field2's value }
    else // else we are on a new row within an old group { if (comparisonVariable != 1) // if we don't start off with a one, we want to
    // preserve current field 2 values {
    comparisonVariable = field2; } } prevField1 = field1; // after comparisons, set prevField to field1 for next comparison }
    comparisonVariable needs to be an output field (in this case, an integer).

    4. Since we can't change values of the input fields in the new 3.0 Javascript (i.e. compatability mode is not checked), we need to do this in a subsequent step. I just followed with a 'Set Values' step to set field2 to be the value in comparisonVariable.

    This doesn't address your question about adjusting previous rows, but hopefully provides an acceptable solution to the end-task that you described.

    I had also some ideas about storing the data from previous rows and then outputing that data as a cloned row when you've had a chance to compare it with the following row. You would output the previous row's data as a new row in the stream, but with an additional 'clone flag' set. You could later filter the stream based on the existence of this cloned flag. The idea is that incoming rows going through a read-only process. It's catch-and-release. There is no chance to write data to those rows. And these rows are clearly identified as being read-only because they don't have the clone flag. Later rows, which do have the clone flag, are marked as read and write. These you woud keep.

    This other idea is very sketchy and has many gaps, and it would only work for comparing pairs of rows anyways.
    Attached Files Attached Files
    Last edited by Matthew Pugsley; 06-26-2009 at 11:49 AM.

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

    Default

    The Analytic Query step offers a way to get your hands on previous/next-row values eliminating the need for JavaScript in a lot of cases.

  4. #4
    Join Date
    Jun 2009
    Posts
    3

    Default

    Thanks gentlemen - both seem like a good way to do things. I haven't upgraded to 3.2 from 3.1, so I will need to do that to play around with the Analytic Query step.

    Matt P: Good thinking and work around, I actually managed to use a workaround with similar logic in a two-step process involving data sorting inbetween. I created a third column that was boolean with true/false values based on the matching criteria of the current row - although your example seems even more flexible than my own.

    Thanks again,

    SD.

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.