¶ Description
This creates or updates new columns based on the other columns in the current row.
¶ Parameters
¶ Standard tab
Parameters:
- Interpreter
- Update (do not create column)
- Variable name
- Meta-type
- Expression
- Input
- Note
¶ Others tab
Parameters:
- On conversion failure caused by negative
- On conversion failure caused by exceeding 2^32-3
- On conversion failure caused by non-integrer
- Add _nowS as input variable for dates saved as strings
- Date format
- Time zone is UTC
- Add _now as input variable for dates saved as Elapsed Time
- Elapsed Time Unit
- Reference time
- Convert empty strings to Null Elapsed Time
- When converting from string to date in Elapsed Time, if time earlier than the reference
- When converting from string to date in Elapsed Time, on format not recognized
- Add _n as input variable for raw numbers
- Seed for pseudo-random numbers (-1 = variable seed)
- Use JIT Compiler (on-the-fly compilation)
- Error handling
¶ About
Here is a first example: We want to compute the “NetProfit” based on the columns “Margin” and “Operating Cost” (NetProfit=Margin-Operating Cost). We’ll have:
Some remarks about the above example:
- We created a new column named “NetProfit”
- To compute “NetProfit”, we used two other columns: “Margin” and “Operating Cost”. Inside the mathematical expression defining the “NetProfit” these 2 columns were, respectively, named “m” and “c”: “m” and “c” are variables inside the mathematical expression “m-c” defining the “NetProfit”. Variable names can use the following characters: a-z,A-Z,0-9,_ (spaces are not allowed in variables names). Variable names cannot start with a number (thus “1A” is an invalid variable name but “_1A” is ok).
- Variables (such as “m” and “c”) can either be of the numerical type or of string type.
You define here: [MISSING] the type (number or string) of each of the variables (in the above example, the 2 variables are of the number type, thus the 2 checkboxes are both enabled). There are no automatic conversions from one type to another: you must use the function atof (to convert a string to a floating-point number), atoi (to convert a string to an integer number), ftoa (to convert a floating-point number to a string) to compute the conversions.
Most operators require a specific type to work: For example, you cannot multiply together two variables of the string type: only variables of the numerical type can be multiplied together. - Trigger completion menu: Press
Ctrl + Spacewhile focused in the Expression field. - The completion menu will display a categorized list of available functions grouped by type (e.g.,
Numbers,Strings,Dates...). - To check if your mathematical expression is valid (e.g., whether you used the correct variable type: numerical or string), click the Help button.
For more information about variable types and available functions, refer to the Help section.
- When you start the pipeline, you will get the following:
The above error message tells you that your variable types are wrong: You defined your 2 variables “Key1” and “Key2” as “integers” (i.e. type ‘i’) but you used them with the string-concatenation-operator (the “//”) that requires the type ‘s’ (i.e. the string type). The solution is simple: click here:
When you click the button again, you get no errors but the result is strange:
(We were expecting “A_B”, as result, but we got “nan” instead). How did we obtain “nan”? The answer is simple: ETL evaluates your expression and correctly obtains “A_B” but, after that, it attempts to store the result “A_B” into a column of the floating-point type:
The result of the conversion of “A_B” to a floating-point number is “nan” (i.e. “Not-A-Number). To correct this, change the meta-type of the new column:
¶ list Of Available Functions
¶ On Numbers
| Function | Description |
|---|---|
| abs(x) | absolute value of x (x<0?-x:x) |
| floor(x) | returns the largest integral value that is not greater than x (round down) |
| floor2(x,y) | returns the largest multiple of y lower or equal to x |
| ceil(x) | returns the smallest integral value that is not less than x (round up) |
| round(x) | round to closest integer |
| round2(x,d) | rounds x to a specified number of digits d |
| roundUp(x,d) | rounds up x to a specified number of digits d |
| roundDown(x,d) | rounds down x to a specified number of digits d |
| rand(x) | uniform random number inside [0 x[ |
| randN(x) | uniform random integer number inside [0 x[ |
| rand1() | uniform random number inside [0 1[ |
| sin(x) | sine function |
| cos(x) | cosine function |
| tan(x) | tangens function |
| asin(x) | arcus sine |
| acos(x) | arcus cosine |
| atan(x) | arcus tangens |
| atan2(y,x) | return the angle between (1,0) and (x,y) |
| cosh(x) | hyperbolic sine |
| sinh(x) | hyperbolic cosine |
| tanh(x) | hyperbolic tangens |
| asinh(x) | hyperbolic arcus sine |
| acosh(x) | hyperbolic arcus cosine |
| atanh(x) | hyperbolic arcus tangens |
| ln(x) | Natural logarithm |
| log(x) | Logarithm base 10 |
| log10(x) | Logarithm base 10 |
| log2(x) | Logarithm base 2 |
| exp(x) | e to the power of x |
| sqr(x) | x² |
| sqrt(x) | square root of x |
| min(x,y,z,...) | Returns the minimum value from all of its function arguments. |
| max(x,y,z,...) | Returns the maximum value from all of its function arguments. |
| sum(x,y,z,...) | Returns the sum value from all of its function arguments. |
| nanvl(x,y) | if x is nan or NULL return y, otherwise return x |
| nanvl2(x,y,z) | if x is nan or NULL return z, otherwise return y |
| finitvel(x,y) | if x is finite return x, otherwise return y |
| finitvel2(x,y,z) | if x is finite return y, otherwise return z |
| isNan(x) | Returns true if x is nan |
| isFinite(x) | Returns true if x is finite (i.e. not nan, not infinite, not neg.infinite) |
¶ On Strings
| Function | Description |
|---|---|
| contains(x,y) | Is y contained in x? |
| indexOf(x,y) | returns the position of y inside x (returns -1 if not found). |
| indexOf2(x,y,p) | returns the position of y inside x starting at position p. |
| lastIndexOf(x,y) | returns the last position of y inside x (returns -1 if not found). |
| lastIndexOf2(x,y,p) | returns the last position of y inside x starting at position p. |
| countText(x,y) | count the number of occurrences of y inside x |
| substr(x,b,l) | extract from the string x a substring beginning at character of index 'b' and of length 'l' |
| mid(x,b,l) | same as above |
| substring(x,b,e) | extract from the string x a substring beginning at index 'b' and ending at index 'e' |
| replace(s,b,a) | Replaces every occurrence of the string <before> with the string <after> and returns result |
| left(s,l) | returns a substring composed of the first 'l' characters of string s |
| right(s,l) | returns a substring composed of the last 'l' characters of string s |
| startsWith(x,s) | return true if the string x starts with the string s |
| endsWith(x,s) | return true if the string x ends with the string s |
| strlen(s) | returns the length of the string s |
| toupper(s) | converts the string s to uppercase characters |
| tolower(s) | converts the string s to lowercase characters |
| toCaseFold(s) | converts the string s to CaseFolded characters |
| trim(s) | returns the string s trimmed |
| ltrim(s) | returns the string s left-trimmed |
| rtrim(s) | returns the string s right-trimmed |
| trim2(x,y) | returns the string x without y at the end/start |
| isNumber(x) | test if the x string contains a number |
| isNumberStr(x) | test if the x string is a number |
| isInteger(x) | test if the x string contains an integer number |
| isIntegerStr(x) | test if the x string is an integer number |
| codepoint(x) | returns the unicode codepoint of the first char of x |
¶ String Encode
| Function | Description |
|---|---|
| atof(s) | converts the string stored in s into a floating point value. |
| ftoa(x) | converts the floating point value stored in x into a string. |
| itoa(x) | convert the integer value stored in x into a string. |
| urlEncode(x) | encode the string x to include in a URL. |
| xmlEncode(x) | encode the string x to include in XML. |
| jsonEncode(x) | encode the string x to include in JSON. |
| atob64(x) | encode the UTF-8 string x to Base64. |
| atob642(x) | encode the Latin-1 string x to Base64. |
| b64toa(x) | decode a Base64 value to a UTF-8 string. |
| b64toa2(x) | decode a Base64 value to a Latin-1 string. |
| htof(x) | convert x (a hexadecimal string) to a number. |
| ftoh(x) | convert x (a positive integer) to a hexadecimal string. |
| htoa(x) | decode a hexadecimal binary string x to a Latin-1 string. |
¶ On Dates
| Function | Description |
|---|---|
| toET(date) | convert a date (as a string) to an Elapsed Time |
| toETF(date) | convert a date (as a string) to an Elapsed Time (as a Float) |
| toDate(ET) | convert a date (as an Elapsed Time) to a string |
| etAdd(ET, y, M, d) | add/subtract a duration (in year, month and days) to an Elapsed Time |
| dateAdd(date, y, M, d) | add/subtract a duration (in year, month and days) to a date |
| dateAddS(date, s) | add/subtract a duration (in seconds) to a date |
| datePrint(date, format) | reformat a date (as a string) |
| etDiffInMonth(ET1, ET2) | number of months between ET1 and ET2 |
| etDiffInYear(ET1, ET2) | number of years between ET1 and ET2 |
| etWeekNumber(ET) | the week number (1 to 53, 0 if error) |
| etDayOfWeek(ET) | the Day of Week (0 = monday; 6 = sunday) |
| etFirstDayOfYear(y, dow) | the ET of the first DayOfWeek(dow) of the year (y) |
| dateWeekNumber(date) | the week number (1 to 53, 0 if error) |
| dateDayOfWeek(date) | the Day of Week (0 = monday; 6 = sunday) |
| dateFirstDayOfYear(y, dow) | the date of the first DayOfWeek(dow) of the year (y) |
| dateDiffInMonth(date1, date2) | number of months between date1 and date2 |
| dateDiffInYear(date1, date2) | number of years between date1 and date2 |
| dateDiffInDay(date1, date2) | number of days between date1 and date2 |
| dateDiffInSecond(date1, date2) | number of seconds between date1 and date2 |
| dateTimeLT(x, y) | true if x is strictly before y (x < y) |
| dateTimeLE(x, y) | true if x is before y (x <= y) |
| dateLT(x, y) | true if date(x) is strictly before date(y) (date(x) < date(y)) |
| dateLE(x, y) | true if date(x) is before date(y) (date(x) <= date(y)) |
| dateEOM(date, x) | similar to "EOMONTH(date,x)" from Excel |
| dateEOQ(date, x) | similar to dateEOM() but for quarters |
| nDaysInMonth(month, year) | return the number of days in the given month |
¶ On IP's
| Function | Description |
|---|---|
| isip4(x) | test if x is a valid TCP-IP v4 address |
| ip4tof(x) | convert a tcp-ip v4 address to a number |
| ip4part(x, pos) | extract one of the 4 parts of a tcp-ip v4 address |
| ip4RangeLow(x) | convert the lower bound of a tcp-ip v4 address-range to a number |
| ip4RangeHigh(x) | convert the higher bound of a tcp-ip v4 address-range to a number |
¶ Operators
| Operator | Description |
|---|---|
| (a?b:c) | Ternary Operator: If a is true then return b, otherwise returns c |
| – | minus operator |
| (float) | convert to float (from boolean or integer) |
| (int) | convert to integer (from boolean or float) |
| +, –, *, /, ^ | Standard operators |
| % | modulo |
| && | Logical And |
| || | Logical Or |
| ==, !=, >, <, <=, >= | Logical Comparison |
| &, |, <<, >> | Bit manipulation (bit-wise AND, bit-wise OR, shift left/right) |
| // | String concatenation |
¶ Predefined Constants
| Constant | Description |
|---|---|
| _pi | 3.141592653589793238462643 |
| _e | Eulerian number (2.718281828459045235360287) |
| _null | the NULL value (meta-type is “number”) – cannot be used inside any binary operator |
| _nullS | the NULL string (meta-type is “string”) – cannot be used inside any binary operator |
| _nan | Not-a-Number (meta-type is “number”) |
| _infinity | Infinity (meta-type is “number”) |
| _now | the current date-time in Elapsed-Time format (if enabled) |
| _nowS | the current date-time in String format (if enabled) |
| _anatellaID | identifier for the current version of Anatella |
| _currentDir | the directory of the current .anatella file |
| _tempDir | the current temp directory |
| _currentFile | the current .anatella filepath |
| _uniqueID | an ID that is guaranteed to be unique |
¶ Postfix Operators
| Operator | Description |
|---|---|
| nano (1e-9) | |
| micro (1e-6) | |
| milli (1e-3) | |
| kilo (1e3) | |
| giga (1e6) | |
| mega (1e9) |
¶ Special
| Function | Description |
|---|---|
| not(x) | boolean not |
| isTrue(x) | return 1 if x is TRUE (1,Y,y,T,t,O,o,P,p), otherwise return 0 |
| isNull(x) | return 1 if x is NULL, otherwise return 0 |
| nvl(x, y) | return y if x is null, otherwise return x |
| nvl2(x, y, z) | return z if x is null, otherwise return y |
| load(filename) | load the number contained in the file |
| nDaysInMonth(month, year) | return the number of days in the given month |
| hdist(lat1, lon1, lat2, lon2) | return the earth distance using Haversine Formula (all coords. in decimal) |
| hdist2(lat1Lon1, lat2Lon2) | return the earth distance using Haversine Formula |
¶ Manipulating Dates-stored-as-Key
You can use the Calculator action to make different computations on Dates&Times in a very efficient way: For example: This will compute the “Number of Elapsed Days Since Activation”, based on the column “ActivationDate” that is converted as a Date-stored-as-Key:
Here is another example: This will compute the “Event Time Of Day” (i.e. “before noon” or “after noon”), based on the column “Event Time” that is converted as a Date-stored-as-Key:
The modulo operator can be used to know the “hour in the day”, the “minute in the day”, the “day in the week”, etc.
¶ Handling NULL Values
Most programming languages (such as Java, C/C++, Delphi, etc.) are using a standard 2-way boolean logic:
Note: “0” means FALSE; “1” means TRUE.
To handle null values, ETL uses the same three-way logic as the standard SQL:
Note: “0” means FALSE; “1” means TRUE; “NULL” is the null value.
This means that the expression (a==_null) will always return null as result. As a consequence, the expression (a==_null?”foo”:”bar”) will always return “bar”. The right way to test if a variable is null is the following: (isNull(a):”foo”:”bar”) or even better (because it’s faster): (nvl2(a,”foo”,”bar”)).
The word “_null” inside an expression cannot be used inside a boolean operation but it’s perfectly valid to use it as “return” value. For example: (a<b:_null:1) or (a<b:_nullS:"1") are perfectly valid expressions (use "_nullS" when the return value is of the string/unknown type and use “_null” when the return value is of the Float or Key type).
¶ Computing many columns
You can compute many different (new) columns inside the same Calculator action. For example, here is an example that computes the column “weight” that is the “weight of the connection between 2 individuals (A and B) inside a telecommunication network”.
As you can see in the screenshot above, the column weight is the arithmetic mean of the 2 variables “WeightAB” and “WeightBA”. These variables are defined on the other tabs inside the Calculator Action:
To be able to use the variables “WeightAB” and “WeightBA” inside the expression of the “Weight”, we must enable these 2 checkboxes:
More precisely, if you want to use a column X inside the expression of the column Y, you need to:
- Enable the “is Input Var” checkbox inside the tab of the column X.
- Move the tab of the column X on the left-side of the tab of the column Y. (because the different expressions are evaluated in the order from the left to the right).
