CODEWITHSUNDEEP
pentahopentaho-spoonpdi
SereniFXX
SereniFXX

Reputation: 11

Loop for Pentaho where I redefine a variable on each execution

I have an excel with 300 rows. I need to use each of these rows as a field name in a transformation.

I was thinking of creating a job that for each row of a table sets a variable that I use afterwards on my transformation.

I tried defining a variable as the value I have in one row and the transformation works. Now I need a loop that gets value after value and redefines the variable I created then executes the transformation. I tried to define a Job that has the following:

Start -> Transformation(ExcelFileCopyRowsToResult) -> SetVariables -> Transformation(The transf that executes using whatever the variable name is at the moment).

The problem is that the variable I defined never changes and the transformation result is always the same because of that.

Upvotes: 1

Views: 1421

Answers (1)

Cyrus
Cyrus

Reputation: 2195

Executing a transformation for each row in a result set is a standard way of doing things in PDI. You have most of it correct, but instead of setting a variable (which only happens once in the job flow), use the result rows directly.

First, configure the second transformation to Execute for each row in the Edit window.

You can then use one of two ways to pass the fields into the transformation, depending on which is easier for you:

  1. Start the transformation with a get rows from result. This should get you one row each time. The fields will be in stream directly and can be used as such.

  2. Pass the fields as parameters, so they can be used like variables. I use this one more often, but it takes a bit more setup.

    • Inside the second transformation, go to the properties and enter variable names you want in the Parameters tab.
    • Save the transformation.
    • In the job, open the transformation edit window and go to Parameters.
    • Click Get Parameters.
    • Type the field name from the first transformation under Stream Column Name for each parameter.

Upvotes: 1

Related Questions