LogoLogo
3.6.9
3.6.9
  • Introduction
  • Getting started
    • Installation
      • Prerequisites
        • Server requirements
      • On-premises
        • Single-node
          • Directory structure
        • Multi-node
      • Azure Market Place
      • AWS Market Place
    • Tutorials
      • Amazon AWS CUR
      • Amazon AWS CUR (Athena)
      • Azure Stack
      • Azure EA
      • Azure CSP
      • Google Cloud
      • VMware vCloud
      • VMware vCenter
    • How-to guides
      • How to configure receiving a monthly billing report
      • How to automatically trigger a monthly billing report
      • How to update your license
      • How to store contract information with an Account in a report
      • How to automatically send workflow errors as webhooks to a monitoring system
    • Concepts
      • User interface
      • Services
    • Releases
      • Upgrading to version 3
      • Known issues
      • Announcements
      • Archive
  • Reports
    • Accounts
    • Services
    • Instances
    • Summary
    • Budget
  • Services
    • Manage
    • Rates
      • Tiered Services
        • Aggregation Levels and the Account Hierarchy
    • Adjustments
    • Subscriptions
  • ACCOUNTS
    • Budget management
  • Data pipelines
    • Extract
      • Configuration
      • Extractor templates
      • Script basics
      • Parslets
      • Subroutines
        • check_dateformat
        • check_dateargument
        • format_date
        • validate_response
      • Language
        • aws_sign_string
        • basename
        • buffer
        • csv
        • clear
        • decimal_to_ipv4
        • discard
        • encode
        • encrypt
        • environment
        • escape
        • exit_loop
        • foreach
        • generate_jwt
        • get_last_day_of
        • gosub
        • gunzip
        • hash
        • http
        • if
        • ipv4_to_decimal
        • json
        • loglevel
        • loop
        • lowercase
        • match
        • pause
        • print
        • return
        • save
        • set
        • subroutine
        • terminate
        • unzip
        • uppercase
        • uri
        • var
    • Transform
      • Configuration
      • Transformer templates
      • Transform Preview
      • Language
        • aggregate
        • append
        • calculate
        • capitalise
        • convert
        • copy
        • correlate
        • create
        • default
        • delete
        • dequote
        • environment
        • event_to_usage
        • export
        • finish
        • Functions
        • if
        • import
        • include
        • lowercase
        • normalise
        • option
        • rename
        • replace
        • round
        • services
        • set
        • sort
        • split
        • terminate
        • timecolumns
        • timerender
        • timestamp
        • update_service
        • uppercase
        • var
        • where
    • Datasets
    • Lookups
    • Metadata
    • Reports
    • Workflows
  • Administration
    • User management
      • Users
      • Groups
    • Notifications
      • Budget Notifications
      • Report notifications
      • Workflow notifications
    • Settings
      • Global Variables
      • White Labeling
  • Advanced
    • Integrate
      • GUI automation
        • Examples
      • API docs
      • Single sign-on
        • Claims-based identity provisioning: users, Account access and user groups
        • Azure-AD
        • Auth0
        • OKTA
        • OneLogin
        • ADFS
        • LDAP
    • Digging deeper
      • Authentication flows
      • Transformer datadate
      • Dataset lifecycle
      • Config.json
      • Databases
  • Security
    • Security
    • Authentication
      • Token
      • LDAP
      • SAML2
    • Password reset
    • Password policy
    • Announcements
  • Troubleshooting
    • Logs
  • Terms & Conditions
  • Privacy Policy
Powered by GitBook
On this page
  • Functions
  • Numeric functions
  • String functions
  • Date functions
  • Transcript-specific functions

Was this helpful?

Export as PDF
  1. Data pipelines
  2. Transform
  3. Language

Functions

PreviousfinishNextif

Last updated 3 years ago

Was this helpful?

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

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

The following functions are supported by the 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.

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

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.

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

@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

All date functions operate with dates in yyyyMMdd format

CURDATE

@CURDATE([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

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
}

FILE_EXISTS

@FILE_EXISTS(filename)

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

FILE_EMPTY

@FILE_EMPTY(filename)

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

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 either case, if the DSET exists and is not empty, the function returns 0.

COLUMN_EXISTS

@COLUMN_EXISTS(column_name)

DSET_ROWCOUNT

@DSET_ROWCOUNT(dset)

This function returns number of rows within specified DSET.

DSET_COLCOUNT

@DSET_COLCOUNT(dset)

This function returns number of columns within specified DSET.

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

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

In 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 mode, a non-existent file is considered equivalent to an existing empty file.

In 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 mode (option mode = permissive), a non-existent DSET is considered equivalent to an existing empty DSET.

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

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

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

if
round
strftime
strict
permissive
strict
permissive
fully-qualified
permissive
permissive