Statement Editor functions

A new home for the Coupa Supply Chain documentation

Starting with Supply Chain 42, our documentation will be located on Coupa Compass. The help here will continue to be accessible for the foreseeable future, but will no longer be updated. If you need a Compass user account, contact your company’s Designated Support Contact (DSC).

You can access new and updated Supply Chain documentation in the following location on Compass:

https://compass.coupa.com/en-us/products/supply-chain-design-and-planning

Statement Editor functions

You can add functions to fields to manipulate data automatically. These functions can be used in any macro or widget that contains the Statement Editor.

You can pass the following items to a function using the Statement Editor:

a string or a numerical value
the value of a parameter
an expression

For example, this Replace function is replacing the parameter value entered by the user with 'abc 123'.

You can edit the statement value directly by clicking the Edit Statement icon . This opens the Statement Text Editor where you can enter the function manually.

For auto incremented columns and computed columns, the Statement Editor is unavailable. These types of columns are computed automatically by the system and cannot be edited.

Using constants and parameters in the Statement Editor

Where appropriate, you can enter a constant value directly into the Statement Editor. To do this, click in the Statement Editor field, then type a single quote ' followed by the constant to use (for example, type '45.5). When finished, press Enter. The data type is determined by the value you enter. Use of a decimal point will result in a float data type.

Some functions include the ability to select parameters from a list or to enter them as text. When this is the case, a toggle appears next to the parameter line:

Use cases

For examples of how to functions can be used, see:

Primary keys

A primary key is a column in a table whose values uniquely identify a row in the table. Primary keys are recommended for all data tables in platform databases.

Some functions will require the specified table to have a primary key.

Your table will need a primary key when you use:

Window functions (for example: Rank, CumulativeSum) in the Conditions definition of a widget
Window functions or Aggregate functions in the Update or Delete actions (Columns, Conditions, or Sort By definitions)

Your table doesn’t need a Primary Key for:

Inline functions (Left, Right, Square, etc.) in the Columns, Sort By, or Conditions definitions of a widget or an action
Aggregate functions in the Columns, Sort By, or Conditions definitions of a widget or an Insert action (for example: Sum, Average)
Window functions in the Columns or Sort By definitions for a widget or an Insert action (for example: Rank, CumulativeSum)
Nested functions. For example: table.string.field.tofloat().square().sum().round(2)

Using functions together

Many functions can be used together in the same statement, action, grid widget, or dynamic view, but some combinations are incompatible in certain situations.

Examples of combinations that work together are:

Inline functions with aggregate functions
Inline functions with window functions

Examples of combinations that can't be used:

Aggregate functions with window functions in the same dynamic view or Insert action
Inline functions with aggregate functions and window functions

These combinations can fail because there may be conflicts between the functions. You can work around this by creating two views, one view with an aggregation function and the second view with a window function or an inline function. The second view can reference the aggregation view.

Functions

Some functions require values to be passed to them. These are indicated by '(...)' following the name of the function. Hover over the function name to see what values need to be passed to the function.

Functions that don't require additional values to be passed to them are indicted by '()'.

Inline functions

These functions apply logic to each row of source data.

Abs(...) — returns the absolute value of a number. Float and Integer data types.

Average() — returns the average value of a set of numbers. Null values are ignored, so they do not affect the average. Float and Integer data types.

AverageZ() — returns the average value of a set of numbers. Nulls are treated as zeros, so they will affect the average. Float and Integer data types.

Ceiling() — returns the smallest integer value greater than, or equal to, a numeric expression. Float and Integer data types.

Contains(...) — returns 1 if the specified expression contains a specific string. When you use this in a condition, you need to compare it to 1. For example, location.code.contains(NA555)=1. String data type.

DateAdd(...) — increments dates according to the arguments. The function returns a date that is the result of adding, to the specified date, the specified numbers of intervals of the specified part of the date.

DateDiff(...) — returns the count of the specified date part boundaries crossed between the specified start date and end date. It returns an Integer data-type.

DateName(...) — returns the month name or day name of the specified Date field. It returns a string data type.

DatePart(...) — evaluates a date and returns a part of the date (for example, the year). Date data type.

DateRollup(...) — returns the first value of the period of time that the date falls within (for example, the value representing the day/week/month). Date data type.

Days() — returns the number of days since January 1, 1900. Date data type.

DoesNotContain(...) — returns1 if the specified expression doesn't contain a specific string. When you use this in a condition, you need to compare it to 1. For example: location.code.doesnotcontain(NA555)=1. String data type.

DoesNotEndWith(...) — checks to see if the specified field does not end with the provided characters. It returns a Boolean (TRUE/FALSE, Yes/No).

DoesNotStartWith(...) — returns 1 if the specified expression doesn't start with a specific string. When you use this in a condition, you need to compare it to 1. For example: location.code.doesnotstartwith(NA555)=True. String data type.

EndsWith(...) — checks to see if the field ends with the provided characters. It returns a Boolean (TRUE/FALSE, Yes/No).

Exp(...) — raises e (the base of natural logarithms) to the power of the argument, where e=2.71828182845905. It returns a Float data-type value.

Floor() — returns the greatest integer value less than a number. Float and Integer data types.

If(...) — Tests the conditions specified in the IF parameter and conditionally returns output from the other parameters. Nested conditions can be specified within the parameters.

IfNull(...) — returns the user-specified alternate value when an expression is NULL. The return type is the same as the input type.

IndexOf(...) — returns the position of the first occurrence of a specified value within a string. Accepts String data types and returns an Integer data type.

InValueRange(...) — determines if the value is within the specified range. It returns a Boolean (TRUE/FALSE, Yes/No).

IsLike(...) — checks to see if the specified string contains something similar to the provided string. This function returns a Boolean (TRUE/FALSE, Yes/No).

IsNotLike(...) — checks to see if the specified string does not contain something similar to the provided string. This function returns a Boolean (TRUE/FALSE, Yes/No).

IsNotNullAll(...) —determines if all specified fields are not void of a value (null). It returns a Boolean (TRUE/FALSE, Yes/No).

IsNotNullAny(...) — determines if any of the specified fields are not void of a value (null). It returns a Boolean (TRUE/FALSE, Yes/No).

IsNotNullOrEmpty(...) — returns 1 if the specified expression is not NULL or empty. When you use this in a condition, you need to compare it to 1. For example: location.code.IsNotNullOrEmpty(NA555)=1. All data types.

IsNotNullOrEmptyAll(...) — determines if none of the expressions are void of a value or are empty. It returns a Boolean (TRUE/FALSE, Yes/No).

IsNotNullOrEmptyAny(...) — determines if at least one of the expressions is not void of a value or is not empty. It returns a Boolean (TRUE/FALSE, Yes/No).

IsNullAll(...) — determines if all specified fields are void of a value (null). It returns a Boolean (TRUE/FALSE, Yes/No).

IsNullAny(...) — determines if at least one of the expressions is void of a value. It returns a Boolean (TRUE/FALSE, Yes/No).

IsNullOrEmpty(...) — returns 1 if the specified expression is NULL or empty. When you use this in a condition, you need to compare it to 1. For example: location.code.IsNullOrEmpty(NA555)=1. All data types.

IsNullOrEmptyAll(...) — determines if all expressions are void of a value or are empty. It returns a Boolean (TRUE/FALSE, Yes/No).

IsNullOrEmptyAny(...) — determines if at least one expression is void of a value or is empty. It returns a Boolean (TRUE/FALSE, Yes/No).

Left(...) — returns the specified number of characters from the left part of a character string. String data type.

Len(...) — returns the number of characters of the specified string expression, excluding trailing blanks. String data type.

Ln(...) — returns the natural logarithm of the argument. This is the inverse of the exponent e^(x). It returns a Float data-type value.

Log(...) — returns the logarithm to the base specified of the argument. It returns a Float data-type value.

Lower() — converts the string to its left to all lowercase characters. String data type.

Ltrim() — returns the string to its left with leading blanks removed. String data type.

NewID(...) — generates a globally unique string ID which can be used when generating data that will reside in a DDM or a UDDB.

NullIf(...) — returns NULL if the expressions are equal; otherwise returns the first expression. The return type is the same as the input type.

OrderBy(...) — defines the logical order of the rows a query's results. OrderBy supports multiple parameters, allowing you to order data by multiple fields. It can be use with PartitionBy to order each partition.

PartitionBy(...) — divides the query results into partitions. The calling function is applied to each partition separately and computation restarts for each partition.

Pow(...) — returns the value of the specified expression to the specified power. It accepts float and integer data types, and returns a float data-type value.

Replace(...) — replaces all occurrences of a specified string value with another string value. String data type.

Right(...) — returns the specified number of characters from the right part of a character string. String data type.

Round(...) — returns the nearest integer value of a number. Float and Integer data types.

Rtrim() — returns the string to its left with trailing blanks removed. String data type.

Sign(...) — returns the positive (+1), zero (0), or negative (-1) sign of the specified numeric expression. Float and Integer data types. Returns an integer data type value.

Square() — returns the square of a number. Float and Integer data types.

SquareRoot() — returns the square root of a number. Float and Integer data types.

StartWith(...) — returns 1 if the specified expression starts with a specific string. When you use this in a condition, you need to compare it to 1. For example: location.code.StartWith(NA555)=1. String data type.

SubString(...) — returns part of a string, starting with specified position. It returns a String data-type value.

ToDateTime(...) — converts the value to its left to a date. Float, Integer, and String data types.

ToFloat(...) — converts the value to its left to a float. Date/time values are converted to the number of days since 1 January 1900. Date, Integer, and String data types.

ToInteger(...) — converts the value to its left to an integer. Date/time values are converted to the number of days since 1 January 1900 (equivalent to the Days() function). Date, Float and String data types.

ToString(...) — converts the value to its left to a string. This function appears in the Statement Editor for integer columns. For Float and Date columns, it can be used in the Statement Text Editor . Integer, Float, and Date data types.

Trim() — returns the string to its left with leading and trailing blanks removed. String data type.

Trunc(...) — removes the fractional part of a rational number, returning the integer portion of the value. Float and Integer data types. Returns an integer data type value.

Upper() — converts the string to its left to all uppercase characters. String data type.

WeightedAverage(...) — calculates the sum of all the values multiplied by their weights, then divided by the sum of the weights. Null values are ignored, so they do not affect the average. Float and Integer data types.

Aggregate functions

These functions automate grouping and aggregating data. When at least one Aggregate function is applied, all statements without an aggregate function are treated as Group By columns. For example, if you have a statement referencing ordersfull.LocationRKey and a statement referencing Ordersfull.Volume.sum() in the same view or action, then the sum statement will return a column with 1 row for each unique Location and a column summing the order volume for each location. There might be any number of rows in the source data, but if there are only 5 unique locations, the result is just 5 rows.

Count() — returns the number of distinct items in a group. NULLs are excluded. All data types.

CountAll() — returns the total number of items in a group. NULLs are excluded. All data types.

CountZ() — returns the number of distinct items in a group. All data types. NULLs are included and are treated as zeros.

CountAllZ() — returns the total number of items in a group. All data types. NULLs are included and are treated as zeros.

Maximum() — returns the maximum of the values in a group of numbers. String, Float, Integer, and Date data types.

MaximumZ() — returns the maximum of the values in a group of numbers. Nulls are treated as zeros. Float and Integer data types.

Minimum() — returns the minimum of the values in a group of numbers. String, Float, Integer, and Date data types.

MinimumZ() — returns the minimum of the values in a group of numbers. Nulls are treated as zeros. Float and Integer data types.

PercentOfSum(...) — returns the percentage that a specified value or group of values represents of the total sum of all values within a specified set of values. Accepts Float and Integer types. Returns a Float data type.

STDEV() — returns the standard deviation of a set of numbers that is a sample of the data. Float and Integer data types.

STDEVP() — returns the standard deviation of a set of numbers that is the entire set of the data. Float and Integer data types.

STDEVPZ() — returns the standard deviation of a set of numbers that is the entire set of the data. Nulls are treated as zeros. Float and Integer data types.

STDEVZ() — returns the standard deviation of a set of numbers that is a sample of the data. Nulls are treated as zeros. Float and Integer data types.

Sum() — returns the sum of a set of numbers. Float and Integer data types. Float and Integer data types.

SumZ() — returns the combined p-values using the sum of z method, also known as Stouffer's method. Nulls are treated as zeros. Float and Integer data types.

VAR() — returns the statistical standard variance of a sample of a set of numbers. Float and Integer data types.

VARP() — returns the statistical standard variance of an entire set of numbers. Float and Integer data types.

Window functions

A window function applies aggregate and ranking functions across a set of table rows that are somehow related to the current row (referred to as a window). Unlike aggregate functions, however, window functions do not collapse the result of the rows into a single value. Instead, all the rows maintain their original identity and the calculated result is returned for every row. If you have 10 rows of source data, there are 10 rows in the result.

Aggregate functions can be turned into window functions by applying the Partition By and Order By functions. In a table, if an aggregate function is changed to a window function using Partition By or Order By, all other aggregate functions need to have their windows explicitly defined or they will not operate as aggregate functions.

CumulativeSum() — returns the running total of a sequence of numbers. A running total is the summation of a sequence of numbers which is updated each time a new number is added to the sequence by adding the value of the new number to the previous running total. This function requires the OrderBy() function. The PartitionBy() function is optional. The DESC option can be used to specify a descending order. Float and Integer data types.

DenseRank() — returns the rank of each row in ascending order within the partition of a result set, with no gaps in the ranking values. The rank of a row is one plus the number of distinct rank values that come before the specific row. All data types. Returns an integer number.

Rank() — returns the rank of each row in ascending order within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question. The DESC option can be added to specify a descending order. All data types.

RowNumber() — returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. All data types.

Last modified: Friday May 12, 2023

Is this useful?

Yes No