Transforming Your Data with JavaScript Code and the JavaScript Step

Steps for PDI Transformation with JavaScript Code

Using the JavaScript language in PDI:
JavaScript is a scripting language primarily used in website development. However, inside PDI you use just the core language; you neither run a web browser nor do you care about HTML.
There are many available JavaScript engines. PDI uses the Rhino engine, from Mozilla. Rhino is an open source implementation of the core JavaScript language; it doesn’t contain objects or methods related to manipulation of web pages.

Inserting JavaScript code using the Modified Java Script Value step:
The Modified Java Script Value step (JavaScript step in short) allows you to insert JavaScript code inside your transformation. The code you type here is executed once per row coming to the step. Let’s explore its dialog window.

• The Transform Functions branch of the tree contains a rich list of functions, ready to use.
• String, Numeric, Date, and Logic categories contain usual JavaScript functions.
• The Special category contains a mix of utility functions.
• Finally, the File category, as its name suggests, contains a list of functions that do simple verifications or actions related to files and folders—for example, fileExist() or createFolder().
• The Input fields branch contains the list of the fields coming from previous steps.
• The Output fields is a list of the fields that will leave the step.

Are you interested in Pentaho training from Intellipaat for getting the best business intelligence jobs?

Adding fields:

1. Define the field as a variable in the code—for example, var totalScore.
2. Fill the grid manually or by clicking the Get variables button. A new row will be filled for every variable you defined in the code.

Modifying fields:
Imagine that you wanted to change the field Skill, converting it to uppercase. To accomplish this, double-click the JavaScript step and add the following two lines:

var uSkill;
uSkill = upper(Skill);

Add the new field to the grid at the bottom:

By renaming uSkill to Skill and setting the Replace value ‘Fieldname’ or ‘Rename to’ to Y, the uSkill field is renamed to Skill and replaces the old Skill field.

Turning on the compatibility switch:

In the JavaScript window, you might have seen the Compatibility mode checkbox. This checkbox, unchecked by default, causes JavaScript to work like it did in version 2.5 of the JavaScript engine.
Time for action – testing the calculation of averages

1. Double-click the JavaScript step.
2. Click on the Test script button.
3. A window appears to create a set of rows for testing. Fill it like here:

4. Click on Preview the transformation. A window appears showing five identical rows with the provided sample values. Close the preview window.
5. Click on OK to test the code.

A window appears with the result that will appear when we execute the script with the test data.

Go through the Pentaho tutorial to learn to develop and building a data mart with Pentaho Data Integration.

Enriching the code:

In the previous section, you learned how to insert code in your transformation by using a JavaScript step. In this section, you will see how to use variables from outside to give flexibility to your code.
Time for action – calculating flexible scores by using variables:

1. Open the transformation of the previous section and save it with a new name.
2. Press Ctrl+T to open the Transformation properties dialog window.
3. Select the Parameters tab

4. Replace the JavaScript step by a new one and double-click it.

5. Expand the Transform Scripts branch of the tree at the left of the window.
6. Right-click the script named Script 1, select Rename, and type main as the new name. 4. Replace the JavaScript step by a new one and double-click it.
7. Position the mouse cursor over the editing window and right-click to bring up the following contextual menu:

8. Select Add new to add the script, which will execute before your main code.

9. A new script window appears. The script is added to the list of scripts under Transform Scripts.
10. Bring up the contextual menu again, but this time clicking on the title of the new script. Select Set Start Script.
11. Right-click the script in the tree list, and rename the new script as Start.
12. In the editing area of the new script, type the following code to bring the transformation parameters to the JavaScript code:

w1 = str2num(getVariable('WEIGHT1',0));
w2 = str2num(getVariable('WEIGHT2',0));
w3 = str2num(getVariable('WEIGHT3',0));
w4 = str2num(getVariable('WEIGHT4',0));
w5 = str2num(getVariable('WEIGHT5',0));
writeToLog('Getting weights...');

13. Select the main script by clicking on its title and type the following code:

var wAverage;
wAverage = w1 * Judge1 + w2 * Judge2
+ w3 * Judge3 + w4 * Judge4 + w5 * Judge5;
writeToLog('row:' + getProcessCount('r') + ' wAverage:' + num2str(wAverage));
if (wAverage >=7)
trans_Status = CONTINUE_TRANSFORMATION;
else
trans_Status = SKIP_TRANSFORMATION;

14. Click Get variables to add the wAverage variable to the grid.
15. Close the JavaScript window.
16. With the JavaScript step selected, click on the Preview this transformation button.
17. When the preview window appears, click on Configure.
18. In the window that shows up, modify the parameters as follows:

19. Click Launch.

20. The preview window shows this data:

21. The log window shows this:

...
2009/07/23 14:46:54 - wAverage with Param..0 - Getting weights...
2009/07/23 14:46:54 - wAverage with Param..0 - row:1 wAverage:8
2009/07/23 14:46:54 - wAverage with Param..0 - row:2 wAverage:8
2009/07/23 14:46:54 - wAverage with Param..0 - row:3 wAverage:7.5
2009/07/23 14:46:54 - wAverage with Param..0 - row:4 wAverage:8
2009/07/23 14:46:54 - wAverage with Param..0 - row:5 wAverage:7.5

Get started with JavaScript closures today by learning more about them in our blog!

Using named parameters:

The parameters that you put in the transformation dialog window are called named parameters. They can be used through the transformation as regular variables, as if you had created them before—for example, in the kettle.properties file.

Also, check out our typescript interview questions to crack interviews.

Using the special Start, Main, and End scripts:

The JavaScript step allows you to create multiple scripts. The Transformation Script list displays a list with all scripts of the step.
The Start Script is a script that executes only once, before the execution of the main script you already know.
The Main script, the script that is created by default, executes for every row
It is also possible to have an End Script that executes at the end of the execution of the step, that is, after the main script has been executed for all rows

Using transformation predefined constants:

In the tree to the left-hand side of the JavaScript window, under Transformation Constants, you have a list of predefined constants. You can use those constants to change the value of the predefined variable, trans_Status, such as:

trans_Status = SKIP_TRANSFORMATION

JavaScript:

1. Create a new transformation.
2. Get the sample file from Packt site and read it with a Text file input step. Uncheck the Header checkbox and create a single field named text.
3. Do a preview. You should see the content of the file under a single column named text. Add a JavaScript step after the input step and double-click it to edit it.
4. In the editing area, type the following JavaScript code to create a field with the code of the property:

var prop_code;
posCod = indexOf(text,’Property Code:’);
if (posCod>=0)
prop_code = trim(substr(text,posCod+15));

5. Click Get variables to add the prop_code variable to the grid under the code.
6. Click OK.
7. With the JavaScript step selected, do a preview. You should see this:

Explore these top Javascript Interview Questions and ace your next interview to get your dream job!