Overview of Functions in EQuIS

<< Click to Display Table of Contents >>

Navigation:  Collect > Collect Enterprise > Template Designer Page > Formula Builder > Functions >

Overview of Functions in EQuIS

Functions can be used in numerous ways to manipulate or transform data, and many functions can be used in combination with others to generate simple or complex formulas. This chapter describes in detail the different functions available in EQuIS Collect and EQuIS Link.

 

Functions are designated as either Standard or Aggregate, which are defined as:

Standard Functions process data within specified parameters from the current row within the data source.

Aggregate Functions process data of the specified parameters from all possible combinations of rows, including the current row, in all used tables within the formula. Aggregate functions iterate over all rows in the tables used, which can require much processing and adversely impact performance. Therefore, it is recommended that they be used only when absolutely necessary.

 

The functions are grouped by their type and include:

Boolean – These functions are used to evaluate and return one of two possible values, denoted as True and False.

Conditional – Conditional functions evaluate a given condition, or set of conditions, and determine if the specified parameters meet the conditions and return a Boolean response of True or False. These functions work with all data types, unless otherwise noted.

Conversion – Conversion functions involve the process of changing a value from one data type to another data type. The functions are only able to compute integer and decimal data.

Math – The math data type includes trigonometric functions, logarithmic functions, and other common mathematical functions. Math functions perform mathematical calculations and are only able to compute integer and decimal data.

String – A string data type is comprised of a set of characters that can also contain spaces and numbers. These functions are only able to compute string field data.

Time – Time functions allow for control of temporal parameters.

Other – The other group contains additional unique functions.

 

 

For each function, the following elements are provided:

Function name

A description of the function, including possible return values

Syntax to use in the formula builder (shown in italics)

Delineation as a standard or aggregate function

Example(s)

 

Note: Within the function syntax denotation, all parameters (Parameter, Numeric Parameter, String Parameter, DateTime Parameter, Optional Parameter) can be a value, function, or constant. Optional Parameters are denoted by { } and may be listed with a descriptor or as Numeric, String, or Boolean.

 

 

Alphabetical Listing

 

ABS

ACOS

ADD

ADDDATE

ADDLEADING

ADDTRAILING

AGGAND

AGGOR

ALLIN

AND

ASIN

ATAN

AUTONUM

AVG

BASE64

BETWEEN

BOTTOM

CASE

CEILING

CHAR

CHECKSUM

CLEAN

CONCAT

CONTAINS

CONTAINSWORD

CORREL

COS

COSH

COUNT

COUNTCHAR

COUNTLIST

COUNTROWS

COUNTTRUE

DATETIME

DAY

DISTINCT

DIV

DIVIDE

DOUBLE

EQ

EXP

FALSE

FILTER

FLOOR

FORMATDATE

FORMATNUM

FROMOADATE

GE

GET

GT

GUID

HOUR

IF

IN

INTEGER

INTERCEPT

ISEMPTY

ISNULL

ISNUMERIC

JARRAY

JGET

JOIN

JSON

LASTN

LE

LEN

LOG

LONGDATE

LOOKUP

LT

MAX

MAXVAL

MIN

MINUTE

MINVAL

MOD

MONTH

MULTIPLY

NOT

NOW

NULL

NUMVALUE

OR

ORD

PARAM

POS

POWER

PRODUCT

QUARTER

RAND

RANDSTR

REGEX

REPLACE

ROUND

SECOND

SHORTDATE

SIN

SINH

SLOPE

SPLIT

SQRT

STABLEDIF

STABLELE

STABLERD

STABLEMAX

STABLEMAXREL

STABLEMAXRELDIF

SUBSTR

SUBTRACT

SUM

TAN

TANH

TIME

TIMESPAN

TODAY

TOLOWER

TOOADATE

TOP

TOUPPER

TREND

TRIM

TRUE

UNITVALUE

YEAR