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

CONTAINS

FIELDEXISTS

JOIN

OR

STABLEMAX

ACOS

CONTAINSWORD

FILTER

JOBJECT

ORD

STABLEMAXREL

ADD

CORREL

FLOOR

JREMAP

PARAM

STABLEMAXRELDIF

ADDDATE

COS

FORMATDATE

JSON

POS

SUBSTR

ADDLEADING

COSH

FORMATNUM

LASTN

POWER

SUBTRACT

ADDTRAILING

COUNT

FROMOADATE

LE

PRODUCT

SUM

AGGAND

COUNTCHAR

GE

LEN

QUARTER

TAN

AGGOR

COUNTLIST

GET

LOG

RAND

TANH

ALLIN

COUNTROWS

GT

LONGDATE

RANDSTR

TIME

AND

COUNTTRUE

GUID

LOOKUP

REGEX

TIMESPAN

ASIN

CUBICSPLINEMAXX

HOUR

LT

REPLACE

TODAY

ATAN

CUBICSPLINEMAXY

IF

MAX

ROUND

TOLOWER

AUTONUM

CUBICSPLINEVALUE

IN

MAXVAL

SECOND

TOOADATE

AVG

DATETIME

INTEGER

MIN

SHORTDATE

TOP

BASE64

DAY

INTERCEPT

MINUTE

SIN

TOUPPER

BETWEEN

DBCONN

ISEMPTY

MINVAL

SINH

TREND

BOTTOM

DISTINCT

ISNULL

MOD

SLOPE

TRIM

CASE

DIV

ISNUMERIC

MONTH

SPLIT

TRUE

CEILING

DIVIDE

JARRAY

MULTIPLY

SORT

UNITVALUE

CHAR

DOUBLE

JFILTER

NOT

SQRT

YEAR

CHECKSUM

EQ

JFIND

NOW

STABLEDIF


CLEAN

EXP

JGET

NULL

STABLELE


CONCAT

FALSE

JJOIN

NUMVALUE

STABLERD