Set Variable In a Pentaho Loop

Question

I tried looking at this: loop-for-pentaho-where-i-redefine-a-variable-on-each-execution, but it's not exactly what I need.

I am using an API to grab results, but I can only grab 100 records at a time. No big deal, I can set the "after" query parameter.

What I need to do is something like this:

1. Start
2. Run transformation that Runs API Initially and grabs the last ID value
3. Run Transformation again but set the "after" query parameter based on the variable in the previous run.
4. If the variable is NULL then move to the next part of the job, if it's not, loop through and run part 3 again.

Any suggestions?

Answer 1

Figured out how to do this. There might be a better way to do this, but this worked for me.

Here is how I accomplished this:

1. Create a job.
2. This job will have start -> transformation -> transformation.
3. In the 2nd transformation, choose to "Execute every input row" on the "transformation options" menu that is accessible by double clicking on the transformation step.
4. Create a sql table that has 1 row and 1 column in it. This value will be our variable.
5. In your transformation, use this value from the table if you want.
6. At the end of your 2nd transformation, update this value.
7. When the transformation runs again, it will use the updated value from the table.
8. This is how you can use changing "variables" in your transformation.