Below, you can find a table with an explanation of every operator you can use in our derived column editor.
Do you want to learn more about how to use derived columns? Read more about how to create one here.
| Operator | Type | Arity | Label | Description | Example |
|---|---|---|---|---|---|
| + | infix | 2 | Sum | Sum two numeric arguments. | 42.0 + Average burrito price |
| - | infix | 2 | Difference | Subtract two numeric arguments. | Burrito weight - 42 |
| * | infix | 2 | Multiplication | Multiply two numeric arguments. | Number of burritos * 2 |
| / | infix | 2 | Division | Divide two numeric arguments. Take care not to divide by zero ;-) | Number of burritos / 2 |
| % | infix | 2 | Modulo | Get the remainder of the integer division of two numeric arguments. | 5 % 2 |
| > | infix | 2 | Greater than | Compare two arguments of the same type. Returns a boolean. | Burrito weight > 50 |
| < | infix | 2 | Less than | Compare two arguments of the same type. Returns a boolean. | Burrito weight < 50 |
| >= | infix | 2 | Greater than or equal | Compare two arguments of the same type. Returns a boolean. | Burrito weight >= 50 |
| <= | infix | 2 | Less than or equal | Compare two arguments of the same type. Returns a boolean. | Burrito weight <= 50 |
| = | infix | 2 | Equal | Compare two arguments of the same type. Returns a boolean. | Burrito type = ‘Savoury’ |
| <> | infix | 2 | Not equal | Compare two arguments of the same type. Returns a boolean. | Burrito type <> ‘Sweet’ |
| ABS | prefix | 1 | Absolute value | Get the absolute value of a numeric argument. | ABS(-5.0) |
| CEIL | prefix | 1 | Round up | Round up a numeric argument to the next integer. | CEIL(11.3) |
| E | prefix | 0 | Euler’s number | Get the Euler constant (0.5772…). | E() |
| EXP | prefix | 1 | Exponential | Calculate the exponential of the numeric argument (eattribute). | EXP(11.3) |
| FLOOR | prefix | 1 | Round down | Round down a numeric argument to the previous integer. | FLOOR(11.7) |
| LN | prefix | 1 | Natural logarithm | Calculate the natural logarithm with base e of the numeric argument. | LN(11.7) |
| LOG | prefix | 2 | Logarithm | Calculate the logarithm with arbitrary base given by the second argument. | LOG(3.6, 10) |
| PI | prefix | 0 | Pi | Get the Pi constant (3.1415…). | PI() |
| POW | prefix | 2 | Power | Calculate x^y^. First numeric argument | PI() |
| RAND | prefix | 0 | Random number | Generate a random number in the range [0,1]. A new number is calculated on each query (but the results might be cached for a while). | RAND() |
| ROUND | prefix | 2 | Round | Round a number to the requested precision. First numeric argument is the number to be rounded, the 2nd numeric argument the requested number of decimals. | ROUND(4.567, 1) |
| SQRT | prefix | 1 | Square root | Get the square root of the numeric argument. | SQRT(4) |
| CAST | infix | 2 | Convert value | Convert the first argument to the type given by the second argument. Possible values are hierarchy, numeric, boolean and datetime.
|
CAST( Burrito weight, hierarchy) |
| IF | prefix | 3 | If | Get a different value according to a condition. The 1st argument is the condition of type boolean. The 2nd argument is the truth value, the 3rd the false value. They must be of equal type. | IF(Burrito weight < 200,‘Small’,‘Large’) |
| COALESCE | prefix | 2 | Is empty | Get the first non-empty argument. The arguments must be of equal type. | COALESCE( Burrito type, ‘Unknown type’) |
| SIN | prefix | 1 | Sine | Calculate the sine of the numeric argument (in radians). | SIN(3.14) |
| COS | prefix | 1 | Cosine | Calculate the cosine of the numeric argument (in radians). | COS(3.14) |
| TAN | prefix | 1 | Tangent | Calculate the tangent of the numeric argument (in radians). | TAN(3.14) |
| ASIN | prefix | 1 | Arcsine | Calculate the arc sine of the numeric argument (in radians). | ASIN(3.14) |
| ACOS | prefix | 1 | Arccosine | Calculate the arc cosine of the numeric argument (in radians). | ACOS(3.14) |
| ATAN | prefix | 1 | Arctangent | Calculate the arc tangent of the numeric argument (in radians). | ATAN(3.14) |
| SINH | prefix | 1 | Hyperbolic sine | Calculate the hyperbolic sine of the numeric argument (in radians). | SINH(3.14) |
| COSH | prefix | 1 | Hyperbolic cosine | Calculate the hyperbolic cosine of the numeric argument (in radians). | COSH(3.14) |
| TANH | prefix | 1 | Hyperbolic tangent | Calculate the hyperbolic tangent of the numeric argument (in radians). | TANH(3.14) |
| || | infix | 2 | Concatenate | Build a string out of 2 other hierarchy arguments. | ‘Tasty’ || ’ burrito’ |
| LENGTH | prefix | 1 | String length | Get the length (in characters) of the hierarchy argument. Returns a numeric. | LENGTH(‘Burrito’) |
| LOWER | prefix | 1 | Lowercase | Convert the hierarchy argument to lowercase. | LOWER(‘BURRITO’) |
| UPPER | prefix | 1 | Uppercase | Convert the hierarchy argument to uppercase. | LOWER(‘burrito’) |
| LTRIM | prefix | 1 | Trim left | Remove all whitespace characters to the left of the hierarchy argument. | LTRIM(’ Burrito’) |
| RTRIM | prefix | 1 | Trim right | Remove all whitespace characters to the right of the hierarchy argument. | RTRIM('Burrito ') |
| TRIM | prefix | 1 | Trim | Remove all whitespace characters around the hierarchy argument. | RTRIM('Burrito ') |
| REPLACE | prefix | 3 | Replace text | Replace all occurrences of a hierarchy. The 1st hierarchy argument is the text in which to search, the 2nd hierarchy argument the text to replace, the 3rd hierarchy argument the text to substitute. Returns a hierarchy. | REPLACE(‘Sweet burrito’,‘Sweet’,‘Savoury’) |
| STRPOS | prefix | 2 | Find substring | Returns the numeric position of the first occurrence of a hierarchy in a text. The 1st hierarchy argument is the text in which to search, the 2nd hierarchy argument the text to search. | STRPOS(‘Sweet burrito’,‘burrito’) |
| SUBSTR | prefix | 3 | Take substring | Returns a substring of a text. The 1st hierarchy argument is the text, the 2nd numeric argument the starting position (the first character of the text is 1), the 3rd numeric argument the length. A negative starting position is interpreted relative to the end of the string. | SUBSTR(‘Sweet burrito’,7,3) |
| NOW | prefix | 0 | Current date/time | Returns the current datetime, calculated on each query (but the results might be cached for a while). | NOW() |
| YEAR | prefix | 1 | Year | Extract the year from the datetime argument. Returns a numeric. | YEAR(‘2016-02-17’) |
| QUARTER | prefix | 1 | Quarter | Extract the quarter from the datetime argument (1 - 4). Returns a numeric. | QUARTER(‘2016-02-17’) |
| MONTH | prefix | 1 | Month | Extract the month from the datetime argument (1 - 12). Returns a numeric. | MONTH(‘2016-02-17 15:36:59’) |
| WEEK | prefix | 1 | Week | Extract the ISO 8601 week from the datetime argument (1 - 53). Returns a numeric. | WEEK(‘2016-02-17 15:36:59’) |
| DAY | prefix | 1 | Day of month | Extract the day of the month (1 - 31) from the datetime argument. Returns a numeric. | DAY(‘2016-02-17 15:36:59’) |
| DAY_OF_WEEK | prefix | 1 | Day of week | Extract the day of the week (1 - 7, 1 is Monday) from the datetime argument. Returns a numeric. | DAY_OF_WEEK(‘2016-02-17 15:36:59’) |
| DAY_OF_YEAR | prefix | 1 | Day of year | Extract the day of the year (1 - 366) from the datetime argument. Returns a numeric. | DAY_OF_YEAR(‘2016-02-17 15:36:59’) |
| YEAR_OF_WEEK | prefix | 1 | Year of ISO week | Extract the year of the ISO week of the datetime argument. Returns a numeric. | YEAR_OF_WEEK(‘2016-02-17 15:36:59’) |
| HOUR | prefix | 1 | Hour | Extract the hour (0 - 23) from the datetime argument. Returns a numeric. | HOUR(‘2016-02-17 15:36:59’) |
| MINUTE | prefix | 1 | Minute | Extract the minute (0 - 59) from the datetime argument. Returns a numeric. | MINUTE(‘2016-02-17 15:36:59’) |
| SECOND | prefix | 1 | Second | Extract the second (0 - 59) from the datetime argument. Returns a numeric. | SECOND(‘2016-02-17 15:36:59’) |
| DATE_ADD | prefix | 3 | Add/subtract duration | Add or subtract a duration to/from a datetime argument. The 1st argument is the duration unit (either ‘millisecond’, ‘second’, ‘minute’, ‘hour’, ‘day’, ‘week’, ‘month’, ‘quarter’ or ‘year’), the 2nd the number of units to add (can be negative), the 3rd the datetime to start with. Returns a datetime. | DATE_ADD(‘hour’, 2, Arrival time) |
| DATE_DIFF | prefix | 3 | Difference between 2 dates | Calculate the difference between 2 datetimes, in the requested precision. The 1st argument is the precision (either ‘millisecond’, ‘second’, ‘minute’, ‘hour’, ‘day’, ‘week’, ‘month’, ‘quarter’ or ‘year’), the time difference is calculated from the 2nd towards the 3rd datetime. | DATE_DIFF(‘day’, Arrival date, ‘2016-02-17 15:36:59’) |
| COORDS | prefix | 3 | Coordinates | Create a set of coordinates based on latitude and longitude, in the requested projection. The 1st argument is the latitude (numeric), the second argument is the longitude (numeric) and the last argument is the projection (e.g. '‘wgs84’, ‘lambert_1972’, …). | COORDS( Latitude, Longitude, ‘wgs84’) |
Don't hesitate to contact us if you have further questions. We're happy to help!