# 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)` .

{% hint style="danger" %}
Function names must be specified in UPPER CASE as shown in the examples below.
{% endhint %}

The following functions are supported by the [`if`](https://olddocs.exivity.io/data-pipelines/transform/language/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.

{% hint style="info" %}
This function rounds half away from zero, e.g. `0.5` is rounded to `1`, and `-0.5` is rounded to `-1`
{% endhint %}

{% hint style="warning" %}
Be careful not to confuse the ROUND function with the [*round* ](https://olddocs.exivity.io/data-pipelines/transform/language/round)statement.
{% endhint %}

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.

{% hint style="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.
{% endhint %}

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

```
@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

{% hint style="warning" %}
All date functions operate with dates in yyyyMMdd format
{% endhint %}

#### CURDATE

```
@CURDATE([format])
```

Returns the current (actual) date in the timezone of the Exivity server. The format may be any valid combination of [strftime ](https://linux.die.net/man/3/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

```
@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

```
@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

{% hint style="success" %}
Transcript-specific functions may be preceded with an exclamation mark in order to negate their output. For example:

```
if (!@COLUMN_EXISTS("colName")) {
   The column colName does NOT exist
}
```

{% endhint %}

#### FILE\_EXISTS

```
@FILE_EXISTS(filename)
```

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

#### FILE\_EMPTY

```
@FILE_EMPTY(filename)
```

In [strict ](https://olddocs.exivity.io/data-pipelines/transform/language/option)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 ](https://olddocs.exivity.io/data-pipelines/transform/language/option)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

{% hint style="warning" %}
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.
{% endhint %}

#### DSET\_EXISTS

```
@DSET_EXISTS(dset.id)
```

Returns 1 if the specified DSET exists, else 0

#### DSET\_EMPTY

In [strict ](https://olddocs.exivity.io/data-pipelines/transform/language/option)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 ](https://olddocs.exivity.io/data-pipelines/transform/language/option)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](http://localhost:8000/general/concepts/datasets/#fully-qualified-column-names), 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 ](https://olddocs.exivity.io/data-pipelines/transform/language/option)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 ](https://olddocs.exivity.io/data-pipelines/transform/language/option)mode (`option mode = permissive`), a non-existent DSET is considered equivalent to an existing empty DSET, and zero is returned.