Go for the Pentaho online training and take your career to the next level.
- The Transform Functions branch of the tree contains a rich list of functions, ready to use.
- 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?
- Define the field as a variable in the code—for example, var totalScore.
- 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.
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:
Time for action – testing the calculation of averages
- Click on the Test script button.
- 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.
Enriching the code:
Time for action – calculating flexible scores by using variables:
- Open the transformation of the previous section and save it with a new name.
- Press Ctrl+T to open the Transformation properties dialog window.
- Select the Parameters tab
- Expand the Transform Scripts branch of the tree at the left of the window.
- 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.
- A new script window appears. The script is added to the list of scripts under Transform Scripts.
- Bring up the contextual menu again, but this time clicking on the title of the new script. Select Set Start Script.
- Right-click the script in the tree list, and rename the new script as Start.
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...');
- 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;
- Click Get variables to add the wAverage variable to the grid.
- When the preview window appears, click on Configure.
- In the window that shows up, modify the parameters as follows:
19. Click Launch.
- The preview window shows this data:
- 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
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.
Using the special Start, Main, and End scripts:
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:
trans_Status = SKIP_TRANSFORMATION
- Create a new transformation.
- 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.
posCod = indexOf(text,’Property Code:’);
prop_code = trim(substr(text,posCod+15));
- Click Get variables to add the prop_code variable to the grid under the code.
- Click OK.