# Functions

### Functions​

A function is used to evaluate one or more arguments and return a result which is then taken into consideration when evaluating the overall truth of the expression.

Function calls start with a the character `@` which is followed by the function name and a comma separated list of parenthesized parameters, for example `@MIN(1, 2, 3)` .

##### danger

Function names must be specified in UPPER CASE as shown in the examples below.

The following functions are supported by the `if` statement:

### Numeric functions​

#### MIN​

``@MIN(number, number [, number ...])``

Return the smallest number from the specified list (requires at least 2 arguments)

Examples:

• `@MIN(1,2)` returns `1`
• `@MIN(1,2,-3)` returns `-3`
• `@MIN(1,2,"-1")` returns `-1` - string `"-1"` is converted to number `-1`
• `@MIN(1,2,3/6)` returns `0.5`
• `@MIN(1,2,"3/6")` returns `1` - string `"3/6"` is converted to number `3`, up to first invalid character
• `@MIN(1,2,"zzz")` returns `0` - string `"zzz"` is converted to number `0`

#### MAX​

``@MAX(number, number [, number ...])``

Return the largest number from the specified list (requires at least 2 arguments)

Examples:

• `@MAX(1,2)` returns `2`
• `@MAX(-1,-2,-3)` returns `-1`
• `@MAX(1,2,100/10)` returns `10`

#### ROUND​

``@ROUND(number [, digits])``

Returns number rounded to digits decimal places. If the digits argument is not specified then the function will round to the nearest integer.

##### info

This function rounds half away from zero, e.g. `0.5` is rounded to `1`, and `-0.5` is rounded to `-1`

##### caution

Be careful not to confuse the ROUND function with the round statement.

Examples:

• `@ROUND(3.1415,3)` returns `3.142`
• `@ROUND(3.1415,2)` returns `3.14`
• `@ROUND(3.1415926536,6)` returns `3.141593`
• `@ROUND(3.1415)` returns `3`
• `@ROUND(2.71828)` returns `3`

### String functions​

#### CONCAT​

``@CONCAT(string1, string2 [, stringN ...])``

This function will treat all its arguments as strings, concatenate them and return the result.

Examples:

• `@CONCAT("the answer ", "is")` returns `the answer is`
• `@CONCAT("the answer ", "is", " 42")` returns `the answer is 42`
• `@CONCAT("the answer ", "is", " ", 42)` returns `the answer is 42`

#### SUBSTR​

``@SUBSTR(string, start [, length])``

Return a sub-string of string, starting from the character at position `start` and continuing until the end of the string end until the character at position `length`, whichever is shorter.

##### info

If length is omitted, then the portion of the string starting at position start and ending at the end of the string is returned.

Examples:

• `@SUBSTR("abcdef", 1)` returns `abcdef`
• `@SUBSTR("abcdef", 3)` returns `cdef`
• `@SUBSTR("abcdef", 3, 2)` returns `cd`
• `@SUBSTR("abcdef", 3, 64)` returns `cdef`

#### STRLEN​

``@STRLEN(string)``

Returns the length of its argument in bytes.

Examples:

• `@STRLEN("foo")` returns 3
• `@STRLEN(@CONCAT("ab", "cd"))` returns 4
• `@STRLEN(1000000)` returns 7 (the number `1000000` is treated as a string)

``@PAD(width, value [, pad_char])``

This function returns value, left-padded with pad_char (`0` by default) up to specified width. If width is less than or equal to the width of value, no padding occurs.

Examples:

• `@PAD(5, 123)` returns `00123`
• `@PAD(5, 12345)` returns `12345`
• `@PAD(1, 12345)` returns `12345`
• `@PAD(5, top, Z)` returns `ZZtop`

#### EXTRACT_BEFORE​

``@EXTRACT_BEFORE(string, pattern)``

This function returns the substring of `string` that precedes the `pattern`. If `pattern` cannot be found in the `string`, or either `string` or `pattern` are empty, result of the function is empty string.

Examples:

• `@EXTRACT_BEFORE("abcdef", "d")` returns `ab`
• `@EXTRACT_BEFORE("abcbc", "bc")` returns `a`
• `@EXTRACT_BEFORE("abcdef", "x")` returns empty string

#### EXTRACT_AFTER​

``@EXTRACT_AFTER(string, pattern)``

This function returns the substring of `string` that follows the `pattern`. If `pattern` cannot be found in the `string`, or either `string` or `pattern` are empty, result of the function is empty string.

Examples:

• `@EXTRACT_AFTER("abcdef", "cd")` returns `ef`
• `@EXTRACT_AFTER("abcabc", "ab")` returns `cabc`
• `@EXTRACT_AFTER("abcdef", "abb")` returns empty string

EXTRACT_XXX functions can be combined to extract the middle part of the string, for example `@EXTRACT_AFTER(@EXTRACT_BEFORE("abcdef", "ef"), "ab")` returns `cd`.

### Date functions​

##### caution

All date functions operate with dates in yyyyMMdd format

#### CURDATE​

``@CURDATE([format])``

Returns the current (actual) date in the timezone of the Exivity server. The format may be any valid combination of strftime specifiers. The default format is `%Y%m%d` which returns a date in yyyyMMdd format.

Examples (assuming run date is 1 July 2019, at 12:34:56):

• `@CURDATE()` returns `20190701`
• `@CURDATE(\"%d-%b-%y\")` returns `01-Jul-19`
• `@CURDATE("%H:%M:%S")` returns `12:34:56`
• `@CURDATE("%u")` returns `1` (weekday - Monday)
• `@CURDATE("%j")` returns `182` (day of the year)

``@DATEADD(date, days)``

Adds a specified number of days to the given date, returning the result as a `yyyyMMdd` date.

Invalid dates are normalised, where possible (see example below):

Examples:

• `@DATEADD(20180101, 31)` returns `20180201`
• `@DATEADD(20180101, 1)` returns `20180102`
• `@DATEADD(20171232, 1)` returns `20180102` (the invalid date `20171232` is normalised to `20180101`)
• `@DATEADD(20180101, 365)` returns `20190101`

#### DATEDIFF​

``@DATEDIFF(end_date, start_date)``

Returns the difference in days between two yyyyMMdd dates. A positive result means that date1 is later than date2. A negative result means that date2 is later than date1. A result of 0 means that the two dates are the same.

Invalid dates are normalised, when possible (see example below):

Examples:

• `@DATEDIFF(20190101, 20180101)` returns `365`
• `@DATEDIFF(20180201, 20180101)` returns `31`
• `@DATEDIFF(20180102, 20180101)` returns `1`
• `@DATEDIFF(20180101, 20180102)` returns `-1`
• `@DATEDIFF(20180101, 20180101)` returns `0`
• `@DATEDIFF(20171232, 20180101)` returns `0` (the invalid date `20171232` is normalised to `20180101`)

``@DTADD(datetime, count [, unit])``

This function adds count number of unit_s (`DAYS` by default) to the specified datetime value and return normalised result datetime value in `YYYYMMDDhhmmss`_ format.

Datetime can be in any of the following formats:

• `YYYYMMDD`
• `YYYYMMDDhh`
• `YYYYMMDDhhmm`
• `YYYYMMDDhhmmss`

All missing bits of datetime value assumed zeros.

Supported units are (both singular and plural spellings supported):

• `YEAR`
• `MONTH`
• `DAY` (default)
• `HOUR`
• `MINUTE`
• `SECOND`

Example

• `@DTADD(20190701, 2)` returns `20190703000000`
• `@DTADD(20190701, 2, HOURS)` returns `20190701020000`
• `@DTADD(2019070112, 50, DAYS)` returns `20190820120000`
• `@DTADD(20190701123456, 10, MONTH)` returns `20200501123456`

### Transcript-specific functions​

##### tip

Transcript-specific functions may be preceded with an exclamation mark in order to negate their output. For example:

``if ([email protected]_EXISTS("colName")) {   The column colName does NOT exist}``

#### FILE_EXISTS​

``@FILE_EXISTS(filename)``

Returns 1 if the file filename exists, else returns 0.

#### FILE_EMPTY​

``@FILE_EMPTY(filename)``

In strict mode, this function returns 1 if the file filename exists and is empty. If the file does not exist, then this is considered an error.

In permissive mode, a non-existent file is considered equivalent to an existing empty file.

In either case, if the file exists and is not empty, the function returns 0

##### caution

FILE_EXISTS and FILE_EMPTY functions will only check files within Exivity home directory and its sub-directories, filename must contain pathname relative to Exivity home directory.

#### DSET_EXISTS​

``@DSET_EXISTS(dset.id)``

Returns 1 if the specified DSET exists, else 0

#### DSET_EMPTY​

In strict mode (`option mode = strict`), this function returns 1 if the specified DSET exists and is empty. If the DSET does not exist, then this is considered an error.

In permissive mode (`option mode = permissive`), a non-existent DSET is considered equivalent to an existing empty DSET.

In either case, if the DSET exists and is not empty, the function returns 0.

#### COLUMN_EXISTS​

``@COLUMN_EXISTS(column_name)``

This function returns 1 if the specified column exists, else 0. The column name may be fully-qualified, but if it is not, then it is assumed to be in the default DSET.

#### DSET_ROWCOUNT​

``@DSET_ROWCOUNT(dset)``

This function returns number of rows within specified DSET.

In permissive mode (`option mode = permissive`), a non-existent DSET is considered equivalent to an existing empty DSET, and zero is returned.

#### DSET_COLCOUNT​

``@DSET_COLCOUNT(dset)``

This function returns number of columns within specified DSET.

In permissive mode (`option mode = permissive`), a non-existent DSET is considered equivalent to an existing empty DSET, and zero is returned.