CloudCheckr FinanceManager Transform Statement: if
The if
statement is used to conditionally execute one or more statements
Syntax
if (conditional expression) {
<statements ...>
} [else {
<statements ...>
}]
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.
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 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 |
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)
.
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)
returns1
@MIN(1,2,-3)
returns-3
@MIN(1,2,"-1")
returns-1
- string"-1"
is converted to number-1
@MIN(1,2,3/6)
returns0.5
@MIN(1,2,"3/6")
returns1
- string"3/6"
is converted to number3
, up to first invalid character@MIN(1,2,"zzz")
returns0
- string"zzz"
is converted to number0
MAX
@MAX(number, number [, number ...])
Return the largest number from the specified list (requires at least 2 arguments)
Examples:
@MAX(1,2)
returns2
@MAX(-1,-2,-3)
returns-1
@MAX(1,2,100/10)
returns10
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.
0.5
is rounded to 1
, and -0.5
is rounded to -1
.Examples:
@ROUND(3.1415,3)
returns3.142
@ROUND(3.1415,2)
returns3.14
@ROUND(3.1415926536,6)
returns3.141593
@ROUND(3.1415)
returns3
@ROUND(2.71828)
returns3
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")
returnsthe answer is
@CONCAT("the answer ", "is", " 42")
returnsthe answer is 42
@CONCAT("the answer ", "is", " ", 42)
returnsthe 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.
Examples:
@SUBSTR("abcdef", 1)
returnsabcdef
@SUBSTR("abcdef", 3)
returnscdef
@SUBSTR("abcdef", 3, 2)
returnscd
@SUBSTR("abcdef", 3, 64)
returnscdef
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 number1000000
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)
returns00123
@PAD(5, 12345)
returns12345
@PAD(1, 12345)
returns12345
@PAD(5, top, Z)
returnsZZtop
Date Functions
CURDATE
@CURDATE([format])
Returns the current (actual) date in the timezone of the CloudCheckr FinanceManager 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()
returns20190701
@CURDATE(\"%d-%b-%y\")
returns01-Jul-19
@CURDATE("%H:%M:%S")
returns12:34:56
@CURDATE("%u")
returns1
(weekday - Monday)@CURDATE("%j")
returns182
(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 normalized, where possible (see example below):
Examples:
@DATEADD(20180101, 31)
returns20180201
@DATEADD(20180101, 1)
returns20180102
@DATEADD(20171232, 1)
returns20180102
(the invalid date20171232
is normalised to20180101
)@DATEADD(20180101, 365)
returns20190101
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 normalized, when possible (see example below):
Examples:
@DATEDIFF(20190101, 20180101)
returns365
@DATEDIFF(20180201, 20180101)
returns31
@DATEDIFF(20180102, 20180101)
returns1
@DATEDIFF(20180101, 20180102)
returns-1
@DATEDIFF(20180101, 20180101)
returns0
@DATEDIFF(20171232, 20180101)
returns0
(the invalid date20171232
is normalised to20180101
)
DTADD
@DTADD(datetime, count [, unit])
This function adds count number of unit_s (DAYS
_ by default) to the specified datetime value and return normalized 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)
returns20190703000000
@DTADD(20190701, 2, HOURS)
returns20190701020000
@DTADD(2019070112, 50, DAYS)
returns20190820120000
@DTADD(20190701123456, 10, MONTH)
returns20200501123456
Transcript-specific functions
if (!@COLUMN_EXISTS("colName")) {
The column colName does NOT exist
}
FILE_EXISTS
@FILE_EXISTS(filename)
Returns 1 if the file filename exists, else returns 0.
system
or exported
(as well as any sub-directories they contain) in the CloudCheckr FinanceManager home directory.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
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.