Pentaho Data Integration setVariable and getVariable issue

Question

I have a job with following transformation in a line:

1) Start

2) calculate_variables

3) use_variables

calculate_variables transformation

This transformation has 3 steps:

step 1: "Table input" step which reads single value my_date from database

step 2: "Add constant values" step which declares a date constant some_date

step 3: "Script Values / Mod" step which is a javascript/java step which uses my_date and some_date to do some calculations and come up with two result variables. One is a number as a string and other is date as a string. At the end of the javascript step I have below statements:

setVariable("NUMBER_VARIABLE", calculatedNumber, "r");
setVariable("DATE_VARIABLE", calculatedDate, "r");

My understanding in Pentaho is limited. But from what I know, this creates two new variables NUMBER_VARIABLE and DATE_VARIABLE and makes them available to every other jobs and transformation as they are set to root.

use_variables transformation

This is where I want to use the variables NUMBER_VARIABLE and DATE_VARIABLE.

Step 1: I have "Generate Rows" as the first step in this transformation.

I want to use NUMBER_VARIABLE as the Limit of Generate Rows. So I have put ${NUMBER_VARIABLE} in the Limit field.

Step 2: "Generate Rows" feeds into a javascript/java step where I am trying to use the DATE_VARIABLE as below:

var sdf = java.text.SimpleDateFormat("yyyy-MM-dd",locale);
var myDate = sdf.parse(DATE_VARIABLE.getString());

My expectation is that this setup should work fine. If NUMBER_VARIABLE is 100, the limit for Generate Rows in next transformation should get set to 100, it should generate 100 rows and then the javascript step should run for those 100 rows and DATE_VARIABLE should be available inside javascript step for use.

When I run this job, calculate_variables transformation runs successfully. But, use_variables transformation fails with the following error:

2018/09/19 10:02:40 - Calculate Dimension Attributes.0 - ERROR (version 7.0.0.1-37, build 1 from 2016-12-21 12.39.46 by buildguy) : Unexpected error
2018/09/19 10:02:40 - Calculate Dimension Attributes.0 - ERROR (version 7.0.0.1-37, build 1 from 2016-12-21 12.39.46 by buildguy) : org.pentaho.di.core.exception.KettleValueException: 
2018/09/19 10:02:40 - Calculate Dimension Attributes.0 - Javascript error: 
2018/09/19 10:02:40 - Calculate Dimension Attributes.0 - ReferenceError: "DATE_VARIABLE" is not defined. (script#9)

I am unable to figure out why this is happening and what can I do to fix this issue.

Edit 1:

When I attach a "Write to log" step to my Generate rows and try to log the variables as:

__________________________ the variables are _______________________

number variable=${NUMBER_VARIABLE}
date variable=${DATE_VARIABLE}
____________________________________________________________________

And then when I run the job, I do get the variables printed successfully:

2018/09/19 10:11:20 - Write to log.0 - __________________________ the variables are _______________________
2018/09/19 10:11:20 - Write to log.0 - 
2018/09/19 10:11:20 - Write to log.0 - number variable=8662
2018/09/19 10:11:20 - Write to log.0 - date variable=2000-01-01
2018/09/19 10:11:20 - Write to log.0 - ____________________________________________________________________

This means the variables are correctly set and are available in the next transformation. Why is the javascript step complaining about it?

Answer 1

I solved it. :)

The issue was the way I was using the variable in Javascript step.

I changed to:

var myDateVariable=getVariable("DATE_VARIABLE","");
var sdf = java.text.SimpleDateFormat("yyyy-MM-dd",locale);
var myDate = sdf.parse(myDateVariable);

Hope this helps somebody stuck like me!