In this tutorial you will redo the previous exercise, but this time you will do the calculations with the Formula step.
- Open the transformation you just finished.
- Delete from the transformation the Calculator step, and put in its place a Formula step. You will find it under the Scripting category of steps.
- Add a field named
writing. - When you click the cell under the Formula column, a window appears to edit the formula for the new field.
- In the upper area of the window, type
[writing]/20. You will notice that the sentence is red if it is incomplete or the syntax is incorrect. In that case, the error is shown below the editing area, like in the following example:
- As soon as the formula is complete and correct, the red color disappears.
- Click OK.
- The formula you typed will be displayed in the cell you clicked.
- Set
Numberas the type for the new field, and typewritingin the Replace value column. - Add three more fields to the grid in the same way you added this field so that the grid looks like the following:
- Click OK.
- Add a second Formula step.
- Add a field named
avgand click the Formula cell to edit it. - Expand the Mathematical category of functions to the leftside of the window, and click the AVERAGE function.
- The explanation of the selected function appears to guide you.
- In the editing area, type
average([writing];[reading];[speaking]; [listening]). - Click OK.
- Set the Value type to
Number. - Click OK.
- Create a hop from this step to the Sort rows step.
- Edit the last Select values step.
- Click Get fields to select.
- A question appears to ask you what to do. Click Clear and add all.
- The grid is reloaded with the modified fields.
- Click on the Dummy step and do a preview.
- There should be no difference with what you had in the Calculator version of the tutorial:
You read the examination.txt file, and did some calculations using the Formula step to see how the students did.
Note
It may happen that the preview window shows you less decimal positions than expected. This is a preview issue. One of the ways you have to see the numbers with more decimals is to send the numbers to an output file with a proper format and see the numbers in the file.
As you saw, you have quite a lot of functions available for building formulas and expressions. To reference a field you have to use square brackets, like in [writing]. You may reference only the current fields of the row. You have no way to access previous rows of the grid as you have in the Calculator step and so you needed two Formula steps to replace a single Calculator. But you saved auxiliary fields because the Formula allows you to type complex formulas in a single field without using partial calculations.
Note
When the calculations are not simple, that is, they require resolving a complex formula or involve many operands, then you might prefer the Formula step over the Calculator.
The Formula step uses the library Libformula. The syntax used in LibFormula is based on the OpenFormula standard. For more information on OpenFormula, you may visit http://wiki.oasis-open.org/office/About_OpenFormula.
Let's play a little with the examination file. Suppose you decide that only those students whose average grade was above 3.9 will pass the examination; the others will not. List the students ordered by average (desc.), last name (asc.), and name (asc.). The output list should have the following fields:
Student codeNameLast NamePassed (yes/no)average grade
Suppose that you want to create a new field as the student_code plus the name of the student separated by a space, as for example 867432-94 Linda Rodriguez. Which of the following are possible solutions for your problem:
a. Use a Calculator, using the calculation a+b+c, where a is student_code, b is a space, and c is the name field.
b. Use a Formula, using as formula [student_code]+" "+[name]
c. Use a Formula, using as formula [student_code]&" "&[name]
You may choose more than one option.