There are many functions, operators, and constants available in *ClarioScript* and the *Expression Builder*.

Function | ClarioScript Call | Return Type | Description |
---|---|---|---|

Absolute | abs(<expr:N>) | Number | Computes the absolute value of a number. |

Add Days | addDays(<date:D>, <days:N>) | Date | Adds or subtracts a number of days to a date. Second arg can be zero or a positive or negative integer. |

Add Months | addMonths(<date:D>, <months:N>) | Date | Adds or subtracts a number of months to a date. Second arg can be zero or a positive or negative integer. |

Arc Cosine | acos(<expr:N>) | Number | Computes the arccosine of a number between -1 and 1. Output is in degrees. |

Arc Sine | asin(<expr:N>) | Number | Computes the arcsine of a number between -1 and 1. Output is in degrees. |

Arc Tangent | atan(<expr:N>) | Number | Computes the arctangent of a number. Output is in degrees. |

Attribute Lookup | attr(<expr:S>) | Attribute | Returns an attribute reference matching a given string value. |

Average | avg(<expr:N>, <expr:N>, ...) | Number | Computes the average of two or more numbers. |

Bound Value | bounds(<valueExpr:N/D>, <lowerBoundExpr:N/D>,<upperBoundExpr:N/D>) | Number or Date | Enforces a lower and upper bound on a value. |

Ceiling | ceil(<expr:N>) | Number | Maps a number up to the next highest integer. Ex. 4.2 becomes 5. |

Concatenate | concat(<expr:S>, <expr:S>, ...) | String | Combines multiple strings into one string. |

Condition | condition(<ifExpr:B>, <thenExpr:ANY>, <elseExpr:ANY>) | Date, String, Number | Returns different expressions depending on whether a specified boolean condition evaluates to true or false. |

Cosine | cos(<expr:N>) | Number | Computes the cosine of a number. Input is a number in degrees. |

Date | date(<year:N>, <month:N>, <day:N>) | Date | Create a new date. |

Date To String | dateToStr(<expr:D>, <formatOfDate:S>) | String | Converts a date to a string. |

Day Of Month | dayOfMonth(<expr:D>) | Number | Parses the numeric day from a date. Ex. 4/1/2011 becomes 1. |

Day Of Week | dayOfWeek(<expr:D>) | Number | Parses the numeric day of week from a date. Uses ISO Standard Monday = 1 through Sunday = 7. |

Days Between Dates | daysBetween(<startExpr:D>, <endExpr:D>) | Number | Computes the number of days between a range of dates. |

End Of Month | endOfMonth(<date:D>, <months:N>) | Date | Add or subtract a number of months to a date and then return the last day of the month for the computed date. Second arg can be zero or a positive or negative integer. |

Exp(x) | exp(<expr:N>) | Number | Computes the exponential function of an attribute, Euler’s number e to the x-th power. |

Floor | floor(<expr:N>) | Number | Maps a number to the next lowest integer. Ex. 4.8 becomes 4. |

Hours Between Dates | hoursBetween(<startExpr:D>, <endExpr:D>) | Number | Computes the number of hours between two dates. |

In | in(<valueExpr: ANY>, <itemExpr: ANY>, ... <itemExpr: ANY>) | Boolean | Returns a Boolean indicating the presence of an attribute or constant within a set of attributes or constants. |

Index | index(<expr:S>,<searchExpr:S>) | Number | Returns the index within a string indicating the first occurrence of the specified search string. The search string can be a Regular Expression. Index is a 0 index based function. |

Length | length(<expr:S>) | Number | Returns the length of a string. |

Logarithm | log(<expr:N>) | Number | Computes the natural logarithm of a number. |

Maximum | max(<expr:N>, ... <expr:N>) | Number | Returns the maximum value of a set of numbers. |

Midnight | midnight(<expr:D>) | Date | Returns a date where the HH:MM:SS is set to 00:00:00. |

Minimum | min(<expr:N>,... <expr:N>) | Number | Returns the minimum of a set of numbers. |

Minutes Between Dates | minutesBetween(<startExpr:D>, <endExpr:D>) | Number | Computes the number of minutes between two dates. |

Modulus | modulus(<dividendExpr:N>, <divisorExpr:N>) | Number | Computes the remainder after division of two numbers. Ex. 5 mod 2 = 1, 16 mod 7 = 2. |

Month Of Year | monthOfYear(<expr:D>) | Number | Parses the numeric month (1-12) from a date. |

Not | not(<expr:B>) | Boolean | Negates any logical expression. |

Not In | notIn(<valueExpr: ANY>, <itemExpr: ANY>, ... <itemExpr: ANY>) | Boolean | Returns a Boolean indicating the absence of an attribute or constant within a set of attributes or constants. |

Now | now() | Date | Returns the date and time as of runtime. |

Number To String | nbrToStr(<expr:N>) | String | Converts a number to a string. |

Pad Left | padLeft(<expr:S>, <lengthExpr:N>, <paddingExpr:S>) | String | Left fills a string with the specified padding string to a total specified length. |

Parentheses | (<expr:ANY>) | Inserts open and close parentheses in an equation or function. Useful when identifying logical groups or order of operations. | |

Power of Value | pow(<baseExpr:N>, <powerExpr:N>) | Number | Raises the base number’s value to the specified power. |

Prior Row | priorRow(<expr:*> | Date, String, Number | Returns the result of the expression applied against the prior row (null for first row). |

Quarter of Year | quarterOfYear(<expr:D>) | Number | Returns a number 1-4 for the quarter of the year in which a date falls. |

Random | random(<seed:N>) | Number | Generates a pseudorandom number based on the specified seed. |

Replace | replace(<expr:S>, <searchExpr:S>, <replaceExpr:S>, ... <searchExpr:S>, <replaceExpr:S>) | String | Replaces the search string with the replace string. Multiple search/replace pairs are applied sequentially. The search string can be a Regular Expression. |

Replace if Value is Null | isNull(<valueExpr:ANY>, replaceExpr:ANY>) | Date, String, Number | Replaces a value, if null, with the replacement expression. |

Round | round(<valueExpr:N>,<precision:N>) | Number | Rounds a value to a specified precision. Ex. precision = 2 rounds to the nearest hundredth, 0 rounds to nearest integer, -2 rounds to nearest hundred. |

Row Count | rowcount(<start:N>) | Number | Count of rows which begins at a specified starting value. |

Seconds Between Dates | secondsBetween(<expr:D>, <expr:D>) | Number | Computes the number of seconds between two dates. |

Selected Attribute | selectedAttribute() | Date, String, Number | Available only in the Treatment node. Implies the treatment will be applied to each selected attribute. |

Sine | sin(<expr:N>) | Number | Computes the sine of a number. Input is a number in degrees. |

Square Root | sqrt(<expr:N>) | Number | Computes the square root of a number. |

String To Date | strToDate(<expr:D>, <formatOfDate:S>) | Date | Converts a string to a date. |

String To Number | strToNbr(<expr:S>) | Number | Converts a string to a number. |

Substring | substring(<expr:S>, <startExpr:N>, <lengthExpr:N>) | String | Returns a subset of a string beginning at the specified starting position and for a specified length. Substring is a 1 index based function. |

Tangent | tan(<expr:N>) | Number | Computes the tangent of a number. Input is a number in degrees. |

To Lower Case | toLowerCase(<expr:S>) | String | Converts a string to all lower case. |

To Upper Case | toUpperCase(<expr:S>) | String | Converts a string to all upper case. |

Today | today() | Date | Returns the date with no time stamp as of runtime. |

Trim | trim(<expr:S>) | String | Trims leading and trailing spaces from a string. |

Variable Get | vget(<name:S>) | Any | Returns the last set value for the given name or null. See Variable Set. |

Variable Set | vset(<name:S>, <value:*>) | Any | Set a variable by associating a name to a value that can later be retrieved using Variable Get. |

Variable Increment | vinc(<name:S>, <value:N>) | Number | Used to create incrementing variables. Shorthand for vset(<name:S>, condition(vget(<name:S>)==?, 0, vget(<name:S>))) |

Verify | verify(<expr:S>, <match:S>) | Boolean | Returns a Boolean indicating the presence of the specified match string within a string. The match string can be a Regular Expression. |

Week Of Year | weekOfYear(<expr:D>) | Number | Returns a value 1-52 for the week of the year in which a date falls. |

Year Of Date | yearOfDate(<expr:D>) | Number | Parses the numeric year from a date. |

Operators are necessary for math and logic expressions. The default operator is ‘+’. Click on the drop-down box in the *Expression Builder* to change this to another operator below.

Operator | Description |
---|---|

+ | Add |

- | Subtract |

/ | Divide |

* | Multiply |

== | Equal to |

!= | Not Equal to |

> | Greater than |

< | Less than |

>= | Greater than or Equal to |

<= | Less than or Equal to |

AND | And |

OR | Or |

Constants are sometimes needed as inputs to functions and can be combined with attribute values. Drag one of the constants into the *Expression Editor*, and then type in its value.

Constant | Description |
---|---|

String | Create an attribute with all values consisting of the specified string |

Number | Create an attribute with all values consisting of the specified number |

Date | Create a date with all values consisting of the specified date |

Null | Create an attribute consisting of all null values |