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. Since aggregate functions iterate over all rows in the tables used, which can require much processing and adversely impact performance, it is recommended that they be used only when absolutely necessary. If a formula contains an aggregate function(s) and multiple tables, each additional table included in the formula has the effect of multiplying the records for each table by the records of each additional table. To maximize the performance of aggregate functions, it is recommended to minimize the number of iterations being calculated by chaining multiple aggregate functions together with each one calculating one table or data set. The chain of aggregate functions can be contained within a non-aggregate function that will calculate the results of each aggregate function. Encapsulating aggregate functions within a non-aggregate function allows the function iterations to be added instead of multiplied, resulting in far fewer calculations. Below is an example showing the recommended approach to maximizing aggregate function performance.

 

Example Aggregate Function Formula (Recommended):

MAXVAL(MAX(FILTER(EQ([#id],[Lithology.#parent_#id]),[Lithology.bottom])),MAX(FILTER(EQ([#id],[Well construction.#parent_#id]),[Well construction.bot_depth])),MAX(FILTER(EQ([#id],[Sample.#parent_#id]),[Sample.sample_end_depth])),MAX(FILTER(EQ([#id],[Well Pipe.#parent_#id]),[Well Pipe.bot_depth])))

This formula shows a function expression that has a non-aggregate function containing multiple chained aggregate functions. Each aggregate function is run independently and MAXVAL, the non-aggregate equivalent of MAX, adds the iterations of each aggregate function.

 

Example Aggregate Function Formula (Not Recommended):

MAX(FILTER(EQ([#id],[Lithology.#parent_#id]),[Lithology.bottom]),FILTER(EQ([#id],[Well construction.#parent_#id]),[Well construction.bot_depth]),FILTER(EQ([#id],[Sample.#parent_#id]),[Sample.sample_end_depth]),FILTER(EQ([#id],[Well Pipe.#parent_#id]),[Well Pipe.bot_depth]))

This formula shows a function expression with multiple aggregate functions chained together, which requires more calculations to be performed. MAX is an aggregate function that multiplies the iterations of each aggregate function, so each aggregate function is run against all of the other aggregate functions.

 

 

Types of Functions

 

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.

JSON – These functions are used to evaluate values configured in a JSON object and/or JSON array.

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

CUBICSPLINEMAXX

CUBICSPLINEMAXY

CUBICSPLINEVALUE

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

JFILTER

JFIND

JGET

JJOIN

JOIN

JOBJECT

JREMAP

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

SORT

SQRT

STABLEDIF

STABLELE

STABLERD

STABLEMAX

STABLEMAXREL

STABLEMAXRELDIF

SUBSTR

SUBTRACT

SUM

TAN

TANH

TIME

TIMESPAN

TODAY

TOLOWER

TOOADATE

TOP

TOUPPER

TREND

TRIM

TRUE

UNITVALUE

YEAR