Page Operations
Browse SpaceDescription
This calculator step provides you with predefined functions that can be executed on input field values. If need other generic, often used functions, visit the Pentaho community page and let Pentaho know about your enhancement request.
Note: The execution speed of the Calculator is far better than the speed provided by custom scripts (JavaScript).
Besides the arguments (Field A, Field B and Field C) you must also specify the return type of the function. You can also choose to remove the field from the result (output) after all values are calculated; this is useful for removing temporary values.
Function List
The table below contains descriptions associated with the calculator step:
| Function | Description | Required fields |
|---|---|---|
| Set field to constant A | Create a field with a constant value | A |
| A + B | A plus B | A and B |
| A - B | A minus B | A and B |
| A * B | A multiplied by B | A and B |
| A / B | A divided by B | A and B |
| A * A | The square of A | A |
| SQRT( A ) | The square root of A | A |
| 100 * A / B | Percentage of A in B | A and B |
| A - ( A * B / 100 ) | Subtract B% of A | A and B |
| A + ( A * B / 100 ) | Add B% to A | A and B |
| A + B *C | Add A and B times C | A, B and C |
| SQRT( A*A + B*B ) | Calculate ?(A2+B2) | A and B |
| ROUND( A ) | Round A to the nearest integer | A |
| ROUND( A, B ) | Round A to B decimal positions | A and B |
| NVL( A, B ) |
If A is not NULL, return A, else B. Note that sometimes your variable won't be null but an empty string |
A and B |
| Date A + B days | Add B days to Date field A | A and B |
| Year of date A | Calculate the year of date A | A |
| Month of date A | Calculate number the month of date A | A |
| Day of year of date | A Calculate the day of year (1-365) | A |
| Day of month of date A | Calculate the day of month (1-31) | A |
| Day of week of date A | Calculate the day of week (1-7) | A |
| Week of year of date A | Calculate the week of year (1-54) | A |
| ISO8601 Week of year of date A | Calculate the week of the year ISO8601 style (1-53) | A |
| ISO8601 Year of date A | Calculate the year ISO8601 style | A |
| Byte to hex encode of string A | Encode bytes in a string to a hexadecimal representation | A |
| Hex encode of string A | Encode a string in its own hexadecimal representation | A |
| Char to hex encode of string A | Encode characters in a string to a hexadecimal representation | A |
| Hex decode of string A | Decode a string from its hexadecimal representation (add a leading 0 when A is of odd length) | A |
| Checksum of a file A using CRC-32 |
Calculate the checksum of a file using CRC-32 |
A |
| Checksum of a file A using Adler-32 | Calculate the checksum of a file using Adler-32 | A |
| Checksum of a file A using MD5 | Calculate the checksum of a file using MD5 | A |
| Checksum of a file A using SHA-1 | Calculate the checksum of a file using SHA-1 | A |
| Levenshtein Distance (Source A and Target B) |
Calculates the Levenshtein Distance:
http://en.wikipedia.org/wiki/Levenshtein_distance\\
|
A and B |
| Metaphone of A (Phonetics) |
Calculates the metaphone of A :
http://en.wikipedia.org/wiki/Metaphone\\
|
A |
| Double metaphone of A |
Calculates the double metaphone of A :
http://en.wikipedia.org/wiki/Double_Metaphone\\
|
A |
| Absolute value ABS(A) |
Calculates the Absolute value of A :
http://en.wikipedia.org/wiki/Absolute_value\\
|
A |
FAQ
How do Length and Precision affect numbers? (Was: Calculator ignores result type on division)
Q: I made a transformation using A/B in a calculator step and it rounded wrong: the 2 input fields are integer but my result type was Number(6, 4) so I would expect the integers to be cast to Number before executing the division.
If I wanted to execute e.g. 28/222, I got 0.0 instead of 0.1261 which I expected. So it seems the result type is ignored. If I change the input types both to Number(6, 4) I get as result 0.12612612612612611 which still ignores the result type (4 places after the comma).
Why is this?
A: Length & Precision are just metadata pieces.
If you want to round to the specified precision, you should do this in another step. However: please keep in mind that rounding double point precision values is futile anyway. A floating point number is stored as an approximation (it floats) so 0.1261 (your desired output) could (would probably) end up being stored as 0.126099999999 or 0.1261000000001 (Note: this is not the case for BigNumbers)
So in the end we round using BigDecimals once we store the numbers in the output table, but NOT during the transformation. The same is true for the Text File Output step. If you would have specified Integer as result type, the internal number format would have been retained, you would press "Get Fields" and it the required Integer type would be filled in. The required conversion would take place there and then.
In short: we convert to the required metadata type when we land the data somewhere, NOT BEFORE.
Q: How do the data types work internally?
A: You might notice that if you multiply an Integer and Number, the result is always rounded. That is because Calculator takes data type of the left hand size of the multiplication (A) as the driver for the calculation.
As such, if you want more precision, you should put field B on the left hand side or change the data type to Number and all will be well.
Hi Matt, I found this table is a bit out of date. Since some functions like "Create a copy from field A" can't be found here.
If I let the Calculator calculate Year of date A with Value type String and Length 4. The value is correctly calculated, but result is a string of 5 characters with one leeding space. What have I done wrong and/or how to work around this?