Search code examples
sqlpentahokettlepentaho-spoon

How to create value Buffer in Pentaho Kettle Transformation

I have some values coming in over time(stream) and over some different rows, which need to be processed as one row.

The incoming data looks kind of like this:

|timestamp   |temp|otherStuff|
|------------|----|----------|
|...         |    |other     |
|04:20:00.321|19.0|other     |
|04:20:01.123|20.5|other     |
|04:20:02.321|22.5|other     |
|04:20:03.234|25.5|other     |
|04:20:04.345|23.5|other     |
|...(new data coming in)     |

What I need could look something like this:

|val0|val1|val2|...|valN  |
|----|----|----|   |------|
|... create new row,      |
|as new data arrives      |
|23.5|25.5|23.5|...|valN  |
|25.5|22.5|20.5|...|valN-1|
|22.5|20.5|19.0|...|valN-2|

I didn't find a good way to solve this with kettle. I'm also using a data service, (Basically a database, with a predefined amount of rows which refresh, as soon as a new dataset arrives) that holds the data in the same way as displayed in the first example.

That means I also could use SQL to flip the table around (which I don't know how to do either). It wouldn’t be as clean as using kettle but it would do the trick.

For better understanding, another example: This is what is coming in:

enter image description here

And something like this is what I need my data to transform to:

enter image description here

Is there any good way of achiving this?

Cheers.

Solution

  • Thank you @jxc,

    the Analytic Query step did the trick.

    Here's a screenshot of how I did it.

    enter image description here

    as @jxc stated, you have to

    Add N+1 fields with Subject = temp, Type = Lag N rows BACKWARD in get Subject and N from 0 to N

    (temp = Value in my case)