Search code examples
etlkettle

Access to previous row in Kettle

In Pentaho ETL Kettle i would like to acces previous row in one step. Let's declare data flow as myMat:

myMat:

      col0  col1
row0: 15    a
row1: 10    b
row2: 24    hi
row3: 11    bye

I would like to use this calculation:

newNumber= myMat[i][0] + myMat[i-1][0]

Do you know any way to do it in Kettle?

Solution

  • You can also do this kind of thing with the Analytic Query step. You'll need a grouping field in your data set though. If you want to process the entire set as a single group, put an Add Constants step before the Analytic Query step that inserts, say a just a 1, and group by that.

    Either way, if you want to use it in a calculation, you'll have to deal with NULL. LAG 1 of the first row of your group will always be NULL. If you just want to map that to a 0, you do that with an If field values is null step. Here is what it might look like:

    Sum of previous row

    However, G Gordon is correct. You should consider if this really makes sense based on the problem you're trying to solve.