I want to design a ETL job as attached.
Step 1 will set two variables Variable_1
and Variable_2
. I am using Generate Rows
step for doing this where I define both variables in Fields
.
Step 2 will read a date from database through a SQL query. I am using Table input
to achieve this. And the sql is like select Variable_3 from table1
which is a single value. Preview here shows that the value is read successfully from database.
I want step 3 as Modified Java Script Value
where I want to use these 3 variables and do some calculation to derive the output Variable_4 which I then want to pass on to future transformation for use there.
I am new to Pentaho and have the following questions:
1) When I connect Step 2 (database step) to Step 3 (Javascript step), I get a warning dialog box:
We detected rows with varying number of fields, this is not allowed in a transformation. The first row contained 2 fields, another one contained 1
What does this warning mean? What am I doing wrong? How can I pass 2 variables from 1st step and 1 from 2nd step to my javascript step?
2) How do I set the calculated Variable_4 in my Javascript step for further use in other transformation? Do I define a Parameter on Transformation 1
named Variable_4
and set it from javascript? If yes, how?
I resolved this by removing the first step (Generate Rows creating two variables) and put in a Add Constants step after Table View step to add Variable 1 and Variable 2 along side Variable 3 passed to it from the Table View step.
Finally, I have all 3 of my variables available in my Javascript step.
I am still not been able to figure out Question 2) though.