# if ## Overview The `if` statement is used to conditionally execute one or more statements ## Syntax ``` if (conditional expression) { } [else { }] ``` ## Conditional Expressions A conditional expression (hereafter referred to as simple an *expression*) is evaluated to provide a TRUE or FALSE result which in turn determines whether one or more statements are to be executed or not. The following are examples of a valid expression: ``` (${dataDate} == 20180801) ((${dataDate} >= 20180801) && ([hostname] == "templateVM")) ``` An expression used by the `if` statement may contain: * Numeric and string literals * Regular expressions * Variables * Operators * Functions ### Numeric and string literals A *literal* is a specified value, such as `4.5` or `"hostname"`. Literals may be numbers or strings (text). If a literal is non-quoted then it will be treated as a number if it represents a valid decimal integer or floating point number (in either regular or scientific notation), else it will be treated as a string. If a literal is quoted then it is *always* treated as a string, thus `3.1515926` is a number and `"3.1415926"` is a string. ### Regular expressions Regular expressions must be enclosed within forward slashes (`/`), and are assumed to be in [ECMAScript format](http://www.cplusplus.com/reference/regex/ECMAScript/). If present, a regular expression must be used on the right hand side of either an `!~` or an `=~` operator, and when evaluated it will be applied to the value on the left hand side of an operator, eg: ``` if (${dataDate} =~ /[0-9]{4}01/) { var first_day_of_month = yes } else { var first_day_of_month = no } ``` As the forward slash is used as a delimiter for the expression, any literal forward slashes required by the expression should be escaped with a back-slash: `\/` ### Variables [Variables](https://olddocs.exivity.io/3.5.4/data-pipelines/transform/language/var) can be used within expressions, in which case they are replaced with their values. Once expanded, these values are treated as literals. ### Operators Operators are evaluated according to the operator precedence rules in the table below (where the highest precedence is evaluated first), unless parentheses are used to override them. Operators with the same precedence are evaluated from left to right. | Precedence | Operator | Meaning | | | | ---------- | -------- | --------------------------------- | -- | ---------- | | 1 | `!` | Unary negation | | | | 2 | `*` | Multiplication | | | | 2 | `/` | Division | | | | 2 | `%` | Modulo | | | | 3 | `+` | Addition | | | | 3 | `-` | Subtraction | | | | 4 | `<` | Less than | | | | 4 | `<=` | Less than or equal to | | | | 4 | `>` | Greater than | | | | 4 | `>=` | Greater than or equal to | | | | 5 | `==` | Is equal to | | | | 5 | `!=` | Is not equal to | | | | 5 | `=~` | Matches regular expression | | | | 5 | `!~` | Does not match regular expression | | | | 6 | `&&` | Boolean AND | | | | 7 | \` | | \` | Boolean OR | {% hint style="info" %} Although expressions are evaluated based on the precedence of each operator as listed in the above table, it is recommended that parenthesis are used within the expression in order to remove any ambiguity on the part of a future reader. {% endhint %} ### 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 parenthesised 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` 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 %} 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/3.5.4/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/3.5.4/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/3.5.4/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/3.5.4/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/3.5.4/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/3.5.4/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.