type: platform

Expressions

An expression is a combination of values, variables, operators, and functions that can be evaluated to produce a result. Think of it as a sentence of instructions that we tell inorigo®, and to get an answer from these instructions we need to write it in a language that inorigo® understands, i.e., expressions.

Overview

Expressions allow you in this way to customize and transform your data from the information you have to the information that you need. They can help you reduce the need for additional attributes and enable you to, for example, apply logic, use formulas, manipulate dates, or filter items.

Where can we use expressions?

Expression can be applied in some way for most modules in inorigo®. Look for an “Expression” text box for where you can add an expression. This can for example be in attribute automation in Model Builder or calculated columns in Knowledge Set Builder.

How to use expressions

Expressions can be as simple as a single value or variable, or they can be more complex, involving multiple values, functions, and operators. Some common types of expressions include Mathematical expressions (such as 2 + 3), logical expressions (such as x > y or a == b), and string expressions (such as "Hello, " + “name”).

A good use for expressions is to manipulate the data that you have created in Model Builder in other modules such as Application Builder and Knowledge Set Builder, but you can also, to some extent, use them in Model Builder. Note that the trickiest part about learning how to use expressions will be understanding how each available function works.

We have categorized the available functions according to their use in this documentation along with descriptions on how you can apply them in expressions.

Vocabulary

Boolean
A value that is either true or false.

Double
A number with decimals, for example 22.343.

Integer
A positive or negative whole number, including 0.

Expression
A combination of variables, constants, operators, and functions that are put together in a single statement to be evaluated.

Function
Always contains $ + Function name and a start and closing parentheses (). All functions are expressions but not all expressions contain functions.

GID
Global Identifier. A way for inorigo® to identify objects.

Input
The data we give to inorigo® to process.

Null
Means that something is undefined. It has no value but note it is not equal to zero.

Output
The result we get back from inorigo® from the processing.

Operate
An action that is carried out to accomplish a given task

Inward function
A function that is enclosed within another function.
Example: $FORMAT_DATE**($GET({p0},"StartDate")**"DATE_LONG")

Outward function
The surrounding function that encloses another function.
Example: $FORMAT_DATE($GET({p0},"StartDate"), "DATE_LONG")

Operator
A character that represents a mathematical or logical action, such as +, -, = or >.

Parse
Converting information into a format. This so to make something understandable for inorigo®.

Parameter
The variable listed inside the parentheses in the function definition.

Argument
The value that are sent to the function when it is called.
Example: X=3, here the 3 is the argument and X is the parameter.

Variable
Container for storing data values.

Constant
A fixed value, for example the number 5. The value does not change and is predefined.

String
A sequence of characters, for example “Hello world”.

Substring
A string that is part of another string. "Hel" is for example a substring of "Hello World".

Syntax
The concept/rules of how different elements in an Expression should be combined in order for it to run correctly.

Zero based Index
A way of numbering in which the initial element of a sequence starts at the index 0 rather than the usual index 1.

$ Functions - Summary List

inorigo® has a number of Built-in functions, which are listed below in alphabetical order.

Name	Description
ABS	Calculates the absolute value of a double value.
ACOS	Calculates the arc cosine of a number.
ADDAY	Add days to a given date.
ADDHOUR	Add hours to a given date.
ADDMILLIE	Add milliseconds to a given date.
ADDMINUTE	Add minutes to a given date.
ADDMONTH	Add months to a given date.
ADDSECOND	Add seconds to a given date.
ADDWEEK	Add weeks to a given date.
ADDYEAR	Add years to a given date.
AGGREGATE	Performs an aggregation on a collection of items.
ASCIITOSTRING	Returns a String object representing the specified decimal ASCII character.
ASIN	Calculates the arc sine of a number.
ATAN	Calculates the arc tangent of a number.
ATINDEX	Returns the value at specified (zero-based) index of a list or array.
AVG	Calculates the average of the given arguments.
CALL	Executes an Inorigo Method and returns the first output of the method.
CEIL	Calculates the smallest (closest to negative infinity) double value that is greater than or equal to the argument and is equal to a mathematical integer.
COALESCE	Return the first non null value.
CONTAINS	Checks if one is contained within two.
COS	Calculates the cosine of a number.
COSH	Calculates the hyperbolic cosine of a number.
COT	Calculates the cotangent of a number (1/tan(n)).
CSC	Calculates the cosecant of a number (1/sin(n)).
DATE_LONG	Formats a timestamp according to Formats a timestamp according to DateTimeLayout.DATE_LONG format.
format.
DATE*SHORT	Formats a timestamp according to DateTimeLayout.DATE_SHORT format.
DATEADD	Adds to a given date depending on DatePart.
DATEFORMAT	Formats a timestamp as a string.
DATEPART	Returns the desired date part of a timestamp.
DATETIME_LONG	Formats a timestamp according to DateTimeLayout.DATETIME_LONG format.
DATETIME_SHORT	Formats a timestamp according to DateTimeLayout.DATETIME_SHORT format.
DAY	Calculate the day of a date, or the current day if no date argument is passed.
DAYNAME	Calculate the day name of a date, or the current day name if no date argument is passed.
DEFINITIONS	Returns a list of definition IDs for one or several instances.
DISTINCT	Performs a distinct operation on a collection of items.
DISTINCTBYCOLUMNS	Creates a Data Set containing column matched unique rows.
E	Calculates the double value that is closer than any other to e, the base of the natural logarithms.
EVALUATE	The function can replace method calls and enhance performance substantially, particularly if used in a calculated column in a knowledge Set with many rows.
EXP	Calculates Euler's number e raised to the power of a number.
EXPR	Evaluates an expression
FILTER	Performs a filter operation on a collection of items.
FILTERNULL	Remove null values from a collection of items.
FINDMANY	Returns all GlobalIDs matching the supplied criterias.
FINDONE	Returns one GlobalID matching the supplied criterias.
FINDSTR	Returns the index of a substring within a string.
FLOOR	Calculates the largest (closest to positive infinity) double value that is less than or equal to the argument and is equal to a mathematical integer.
FOREACH	Executes an expression for each item in a collection of items.
FORMAT_DATE	Formats a timestamp as a string.
FORMAT_NUMBER	Formats a number using the current locale.
GET	Returns an Attribute Value. Can also be used to get a hash map value.
GID	Creates a GlobalID.
HOUR	Calculate the hour of a date, or the current hour if no date argument is passed.
HTML_ENCODE	Makes a string safe to present on web.
IFNULL	Returns second or third argument, depending on wether first argument is null.
IIF	Returns second or third argument, depending on boolean first argument.
IS_AUTHORIZED	Check entity for authorization.
ISNULL	Returns wether first argument is null.
KNOWLEDGE_SET	Executes a Knowledge Set.
LEFT	Returns the leftmost _n* characters of a string.
LOAD	Deprecated.
LOG	Calculates the natural logarithm (base e) of a number.
LOG10	Calculates the base 10 logarithm of a number.
LOOKUP	Performs a lookup by attribute values.
LOOP	Executes an expression for each item in a collection of items.
MAX	Calculates the greater double value of two numbers.
MILLISECOND	Calculate the millisecond of a date, or the current millisecond if no date argument is passed.
MIN	Calculates the smaller double value of two numbers.
MINUTE	Calculate the minute of a date, or the current minute if no date argument is passed.
MONTH	Calculate the month of a date, or the current month if no date argument is passed.
MONTHNAME	Calculate the month name of a date, or the current month name if no date argument is passed.
NOW	Calculates the current date and time.
NTH	Returns the n:th object in a list or the n:th letter in a string.
NULL	Returns null.
OBJECT	Returns an GlobalID using the supplied identification.
PACK	Returns a list of the arguments supplied.
PARTNERS	Returns a list of partner Global Identifiers.
PERCENT	Creates dataset with added percentage column(s).
PI	Calculates the double value that is closer than any other to pi, the ratio of the circumference of a circle to its diameter.
POW	Calculates the double value of the first number raised to the power of the second number.
QUARTER	Calculate the quarter of a date, or the current quarter if no date argument is passed.
QUARTERNAME	Calculate the quarter name of a date, or the current quarter name if no date argument is passed.
REFERENTS	Returns Attribute Value referents for a given instance.
REPLACEREGEX	The function can replace method calls and enhance performance substantially.
REPLACESTR	Replace part(s) of a string. It is used to replace parts of a string using a regular expression.
RIGHT	Returns the rightmost n characters of a string.
ROUND	Rounds the first argument to the number of decimals specified by the second argument, using the HALV_EVEN rounding mode.
SEC	Calculates the secant of a number (1/cos(n)).
SECOND	Calculate the second of a date, or the current second if no date argument is passed.
SELECT	Select rows and columns from a Dataset or a List of Dataset rows.
SERIAL_NO	Generates the next serial number for the given pool.
SIN	Calculates the sine of a number.
SINH	Calculates the hyperbolic sine of a number.
SIZE	Performs a size operation on a value.
SORT	Performs sorting on a collection of items.
SQRT	Calculates the square root of a number.
STRING_JOIN	Returns a string built from a list of items, with specified delimiter and optional prefix and suffix
STRINGFORMAT	Performs a string.format() operation.
SUBCLASSES	Returns a list of subclasses.
SUBSTR	Returns a substring of the first input string.
SUM	Returns summary of values.
SUPERCLASSES	Returns a list of superclasses.
TAN	Calculates the tangent of a number.
TANH	Calculates the hyperbolic tangent of a number.
TIME_SHORT	Formats a timestamp according to DateTimeLayout.TIME_SHORT format.
TIME_SHORT_MILLIES	Formats a timestamp according to DateTimeLayout.TIME_SHORT format.
TIMESPAN	Calculates the timespan between two timestamps or a duration.
TO_COORDINATE	Creates a valid Coordinate for use in maps.
TOARRAY	Creates an array of the supplied input.
TODATE	Parse argment to timestamp.
TODATETIME	Parse argment to timestamp.
TODOUBLE	Parse argment to double
TOGLOBALID	Creates a GlobalID.
TOINT	Parse argment to integer
TOLIST	Creates a List of the supplied input.
TOLONG	Parse argment to long
TOLOWER	Turns a string into lower case.
TOLOWERCASE	Turns a string into lower case.
TOSTRING	Returns argument as its string representation.
TOULONG	Parse argment to unsigned long (BigInteger)
TOUPPER	Turns a string into upper case.
TOUPPERCASE	Turns a string into upper case.
TRANSLATE	Translates a text.
TRIM	Removes leading and trailing spaces from a string.
VELOCITY_IFRAME	Returns an IFRAME HTML tag for a Velocity page.
VELOCITY_URL	Returns an URL for a Velocity page.
WEEK	Calculate the week of a date, or the current week if no date argument is passed.
YEAR	Calculate the year of a date, or the current year if no date argument is passed.
YEAR_QUARTER	Calculate the year and quarter name of a date, or the current year and quarter name if no date argument is passed.

Legacy Expression Editor

The below are relevant for the Model Builder and Application builder

Expression Editor

The Expression Editor will simplify the readability of expressions as it checks for inconsistencies and helps you with some guidance and basic validations to create the expression.

Note, the Visible option is not always available, it depends on the context expression is used, e.g. it is useful when writing expressions for map Info panel.

The Functions context menu options will show all the available Expression Functions

and they are inserted in Expression field as they are selected.

There are a few shortcuts to some of the most common operations/variables/functions used;


Ctrl-G	$GET
Ctrl-I	@item (the Source object in map configuration)
Ctrl-N	“name”
Ctrl-R	@record

Variables – Verso View will give the list of available default variables, user defined variables and dataset(s) as input to new Expression Variables in view;

Example that shows the available items defined in view

Sometimes the contents of the context menu are not updated, especially for some Variables and Datasets that require calculations to be updated, hence the Refresh option to update the list.

Expression Reference Guide

Advanced Functions

$CALL

Executes an Inorigo Method and returns the first output of the method.

Input:

String methodName , Any param1, Any param2,...

Output:

Any

Usage:

$CALL(Target Global Identifier [, method input, ...])
$CALL(Method Unique Identifier [, method input, ...])
$CALL(Method Fully Qualified Name [, method input, ...])

Example:

$call("MyChange/HelloWorld", "Hey", "You"). This will run the method "HelloWorld" in the process object "MyChange" and give "Hey" and "You" as input variables.

$Coalesce

Return the first non null value.

Input:

Object or list/collection

Output:

Any

Usage:

$COALESCE(@NULL, 1, 2, "test") → 1

Example:

We have associations of the definition Person in a filter box. The below expression will output the first non null item in the selected lis

This expression is first checking if the first selected item in p0 is null via $NTH({p0}, 0), if null, it will return "Nothing is selected in p0!" but if any value is found it will return the first non null item.

Result below. Notice how the output changes accordingly to whether if a item in a higher order is selected.

$EXPR

Evaluates an expression

Input:

String expression

Output:

Any

Usage:

$EXPR("Hello inner world.").

$IS_AUTHORIZED

Check entity for authorization.

Input:

Entity or Entity ID.
Action (Read / Update / Create / Delete / Execute).
Optional User ID. If empty, current user is used.

Output:

True or False.

Usage:

$IS_AUTHORIZED(@item, "Read")

$IS_AUTHORIZED(@item, "Read", @USER_ID)

$IS_AUTHORIZED(@item, "Read", "F973B5C0-21B3-D4AB-870D-A42201230315")

Example:

We have a filter box (p0) containing associations of the definition Person which we are showing in a result matrix by using p0.all.

We add the below expression as a calculated column which will check if the current user is able to delete each of the associations in the p0:

$KNOWLEDGE_SET

Executes a Knowledge Set. Returns the execution result as a DataSet, suitable for further processing

Usage:

$KNOWLEDGE_SET(@KsID).

$KNOWLEDGE_SET("7f69e05c-5adc-4bce-a88b-0640c84792e5").

Example:

We have a Knowledge Set containing associations from the definition Person with the attribute Columns below:

We want to view this data structure in Application Builder as well. So we add our below expression and input the ID of the Knowledge Set to a Result Matrix in Application Builder:

$KNOWLEDGE_SET(aaa52881-f7af-78e9-1cec-b00400807535)

This will result in below view:

$Null

Returns null.

Usage:

$NULL().

$SERIAL_NO

Generates the next serial number for the given pool.Returns a number of type long

Usage:

$SERIAL_NO("GENERAL_POOL").

$STRINGFORMAT("EMP-%02d", $SERIAL_NO({POOL_NAME}))

Example:

We want to create create an expression that generates serial number for our Serial Pool named Test_Pool. This should then be used in an automation for an attribute called Serial Number within a definition named Person.

We first create the Serial Number Pool in System Management;

We then call the Serial Number Pool we just created in the automation panel within the attribute:

When we create a new association for the definition we can see the Serial Number attribute. Note that it will not generate until we save the association.

Now we have saved the new association and we can see the generated Serial Number:

$TO_COORDINATE

Creates a valid Coordinate for use in maps.

Usage:

$TO_COORDINATE(@MyLatitude, @MyLongitude).

$TO_COORDINATE(@MyStringCoordinate).

$VELOCITY_IFRAME

Returns an IFRAME HTML tag for a Velocity page.

Usage:

$VELOCITY_IFRAME("de305d54-75b4-431b-adb2-eb6b9e546014") $VELOCITY_IFRAME("HelloWorld.html")

$VELOCITY_IFRAME("de305d54-75b4-431b-adb2-eb6b9e546014","RUNTIME_ID=" + @RUNTIME_ID + "&CLICK="+@CLICK)

$VELOCITY_URL

Returns an URL for a Velocity page.

Usage:

$VELOCITY_URL("de305d54-75b4-431b-adb2-eb6b9e546014") $VELOCITY_URL("HelloWorld.html")

$VELOCITY_URL("de305d54-75b4-431b-adb2-eb6b9e546014","RUNTIME_ID=" + @RUNTIME_ID + "&CLICK="+@CLICK)

Aggregation functions

What is an Aggregate function?

These functions operates on a set of values and returns a single value as an output.

Note:

Ignores null values.

Performs an aggregation on a collection of items. Optionally grouped.

$AGGREGATE

Performs an aggregation on a collection of items. Optionally grouped.

Input:

Items - a collection of items supported by $GET() (i.e GlobalID, DataSetRow, HashMap, SelectorItem etc)

Aggregation Type - case insensitive. (Sum, Count, Average, Median, Min, Max, StdDeviation, Variance, Percentage)

Aggregation Column(s).

Optional - Group Column(s).

Output:

Always returns a DataSet.

Aggregation columns will be suffixed with the type of aggregation (e.g "Income (Sum)").

If no Group column(s) has been supplied a default "Total" column will automatically be generated and aggregation result(s) will start at column 1.

Usage:

$AGGREGATE({Dataset}, "Sum", "Amount")

$AGGREGATE({Dataset}, "Sum", "Amount", "Category")

$AGGREGATE({Dataset}, "Average", "Income", "Gender", "Age")

$AGGREGATE({p0}, "Average", $PACK("Income","Tax"), "Gender", "Age")

$AGGREGATE({p0}, "Median", $PACK("Income","Tax"), $PACK("Gender", "Age"))

Example:

In this example we have a filter box (p0) containing associations of the definition Person with an Employment and Age Attribute. We want to see the average age per Employment in a result matrix.

What we can do to achieve this is to use the aggregate function in an expression where we specify our Aggregation type as "AVG" and then add our Aggregation and group column (Age, Employment) :

$AGGREGATE({p0}, "Average", "Age", "Employment")

 

$AVG

Calculates the average of the given arguments.

Input:

List of numbers

Output:

Double

Usage:

$AVG(List of Numbers)

$AVG(List of Dates)

$AVG(List of Entities, attribute name or id)

$AVG(List of Global Identifiers, attribute name or id)

Example:

We have a filter box p0 containing associations of the definition Person with an Age attribute. We want to see the average age of all persons in p0. We therefore use the below expression in a calculation box, which tells Inorigo to calculate the average of the all the age attribute values in p0:

$AVG({p0.all}, "Age")

$MAX

Calculates the greater double value of two numbers.

Input:

Double, Double

Output:

Double

Usage:

$MAX(@myFirsNumber,@mySecondNumber)

$MAX(List of Numbers)

$MAX(List of Dates)

$MAX(List of Entities, attribute name or id)

$MAX(List of Global Identifiers, attribute name or id)

Example 1:

We can use $MAX to return the largest number/date in a list. We have associations of the definition Person listed in the filter box p0 that has the attribute StartDate which consist of a date. Let's say we want to see the latest date in for all these persons in p0, we can then use the below expression in calculation box:

Example 2:

We have associations of the definition Person listed in p0 which have an attribute called Scope. What we want to do is to check if the selected persons in p0 together have a scope value larger than 200. If not then return 200, and if yes then return the sum of the selected scope values. In the first image you can see that we have selected Sara Bloom, which have Scope of 90. But since it's not larger than the 200 we specified it will not change in the top calculation box

$MAX($SUM({p0},"Scope"), 200)

Returns 200 or the selected sum of Scope in p0, whichever being the largest.

But now we select more people which brings our total scope values to that of 205. The top calculation box now changes to 205 as the sum is over 200.

$MIN

Calculates the smaller double value of two numbers.

Input:

Double, Double

Output:

Double

Usage:

$MIN(@myFirsNumber,@mySecondNumber)

$MIN(List of Numbers)

$MIN(List of Dates)

$MIN(List of Entities, attribute name or id)

$MIN(List of Global Identifiers, attribute name or id)

Example 1

We can use $MIN to return the smallest number/date in a list. Here we have persons associations listed in p0 (Person) which has the attribute StartDate which consist of a date. Let's say we want to see the earliest date in for all these persons in p0, we can then use the below expression in calculation box:

$MIN($GET({p0}, "StartDate"))

Example 2

$MIN($SUM({p0},"Age"), 50)

Returns 50 or the sum of p0, whichever being the smallest.

In this example we have a filter box containing associations of the definition Person with the attribute age. We want to see if the sum of the selected ages in the filter box p0 (Person) is below 50, if yes return return the sum of selected ages, if no then return 50.

Here we have only selected one person that have an age of 35, and since it is smaller than 50, the top Calculation box returns the sum of ages selected (35).

But here we have selected more people in the filter box which brings our total sum of the ages combined to 98. And since this age sum is larger than 50 the expression will return 50 in the top result Calculation box.

$PERCENT

Creates a dataset with added percentage column(s).

Input:

Items. A collection of DataSet rows, like a normal dataset or the result of an Aggregation

Value Column(s).

Optional - Total Grouping Column(s).

Output:

Always returns a DataSet.

Aggregation columns will be suffixed with % (e.g "Income %").

If no Grouping column(s) has been supplied, the total is simply the sum of all values in the Value column.

Usage:

$PERCENT({Dataset}, "Amount", "Category")

$PERCENT({Dataset}, "Income", "Gender", "Age")

$PERCENT({Dataset}, $PACK("Income","Tax"), "Gender", "Age")

$PERCENT({Dataset}, $PACK("Income","Tax"), $PACK("Gender", "Age"))

Example:

In the below example we have a dataset (DS) containing associations of the definition Person with the attributes Employment, and Income.

Using the below expression in a Result Matrix will output the percentage of the total Income per Employment for each row in the dataset DS. Where our dataset is the items input, Income is the value column and Employment is the total grouping column

$PERCENT({DS}, "Income", "Employment")

 

$SUM

Returns summary of values.

This function is used for summarizing lists, plus a reference to the object attribute value to be summed.

Input:

Collection of numbers or Collection<GID/DataObject>, String attribute

Output:**

Double

Usage:

$SUM(List of Numbers)
$SUM(List of Dates)
$SUM(List of Entities, attribute name or id)
$SUM(List of Global Identifiers, attribute name or id)

Example:

In this example we have a filter box p0 containing associations of the definition Person with the attribute age. We want to see the sum of all the selected age attributes values in a calculation box, so we add the below expression. Here p0 is the collection of items input and "Age" is our string attribute:

$SUM({p0}, "Age")

Collection Functions

What is a collection Function?

These functions operate on or return collections.

$CONTAINS

Checks if a string contains another string.

Input:

Look for: Any type.

Look in: String / Collection or Array

Output:

True / False.

Usage:

$CONTAINS("World", "Hello World") -> True

$CONTAINS(@MyGID, {p0}).

$CONTAINS("David", $GET({p0}, "First Name"))

Example:

We have a filterbox p0 containing associations of the definition Person with the attribute’s First Name, Last Name, Employment and Email.

Here is the default matrix where we simply have all the items from p0 along with their attribute values. Note that some of these have "gmail" in their email address

We want to see if any people have a gmail account as their email address. What we can do is see if the email attribute value contains “gmail” via the below expression.

The expression will check if each row in the Email column contains “gmail”. It will then return either true or false for each row.

$CONTAINS("gmail", $GET({record}, "Email"))

$DISTINCT

This function will only return distinct values I.e., duplicate values will not show.

Input:

Items - A collection of items supported by $GET() (i.e GlobalID, DataSetRow, HashMap, SelectorItem etc)

Distinct Column(s).

Output:

Always returns a DataSet.

Usage:

$DISTINCT({DataSet}, "Gender", "Age")

$DISTINCT({p0}, $PACK("Gender", "Age"))

Example:

In the below example we have a Filter Box (p0) containing the Associations from the Definition Person. These associations have the attribute Employment and Income.

We want to see the distinct incomes per employment from our filter box p0. We therefore use below expression:

$DISTINCT({p0}, "Employment", "Income")

As you can see, we only have 19 rows in the “DISTINCT” matrix using the expression even though we have selected 21 people in the filter box. This is because we have 2 different sets of people that have the same attribute value in Income and Employment, so the expression only returns one distinct value for each of these duplicates.

Here are the people with the same income and employment:

$DISTINCTBYCOLUMNS

Creates a Data Set containing column matched unique rows.

Note! The result will contain copies of the distinct rows with ALL original columns.

Input:

Rows. A collection of Data Set Rows (like a dataset).

First? True: Return the first row found. False: Return the last row found (slower).

Distinct Column(s). An invalid column will render an empty result!

Output:

Always returns a DataSet.

Usage:

$DISTINCTBYCOLUMNS({Dataset}, true, "Gender", "Age")

$DISTINCTBYCOLUMNS({Dataset}, true, 1, 3)

$DISTINCTBYCOLUMNS({Dataset}, true, $PACK("Gender", "Age"))

$DISTINCTBYCOLUMNS({Dataset}, true, @MyColumnsVariable)

Example:

In the below example we have a dataset containing associations of Definition Person with attributes Income and Scope but we want to only show the rows that have a distinct value in both the Income and Scope columns.

Here is the default view of the dataset in a result matrix

So, we add the below expression to a matrix and the result is 19 rows compared to the previous 21 rows from the dataset.

The reason we get 19 rows is that we have 2 different sets of duplicate values for Income and Scope in the dataset (Lisa Kudrow & Adam McKay and Anders Karlsson & Elie Wiesel). Lastly, as we have added true to our input in the expression, we get the first row found for the duplicates (Adam McKay and Anders Karlsson) and Lisa Kudrow and Elie Wiesel is excluded from the list resulting in 19 rows.

$DISTINCTBYCOLUMNS({DS}, true, "Scope", "Income")

$FILTER

Performs a filter operation on a collection of items.

The expression will be evaluated for each item in the input list.
The item can be referenced in the expression by {record}.

Input:

A collection of items. Each item can be of any type supported by the filter expression.
A Boolean expression for filtering the items.

Output:

A list of items for which the supplied expression returned true.

Usage:

$FILTER({Dataset}, $GET({record}, "Age") > 50)

$FILTER({p0}, $GET({record}, "Employment")="Inpraxius")

Example 1

We have a dataset (DS) containing associations of the Definition Person with the attributes Employment, Gender and Age. We want to filter out the rows of people with an age below 29.

Here is the default dataset DS in a result matrix

We add the below FILTER expression to a result matrix, which will only output the rows in DS that have an Age attribute value higher than 29. As you can see we now only have 17 rows compared to the previous 21.

$FILTER({DS}, $GET({record},"Age") > 29)

Example 2

Here we have the Filter box p0 containing associations of the definition Persons with the attribute Gender. We want to only show the associations with an attribute value of "Woman" as an output in a calculation box. So what we can do is the the expression below:

$FILTER({p0.all}, $GET({record}, "Gender") = "Woman")

This expression will filter all items in p0 and only return those records/items that have Gender set as Woman

Note: if you want to add an initial selection filter to p0 that will select these person in the filter box, you can add the below expression. Simply add the previous filter expression to "{record} ="

{record} = $FILTER({p0.all}, $GET({record}, "Gender") = "Woman")

$FILTERNULL

This function removes null values.

Input:

A collection of items.
Optional attribute to check if list contains items supported by $GET.

Output:

List, Dataset.

Usage:

$FILTERNULL(@DataSet, "Gender")

$FILTERNULL(@ListOfNumbers)

$FILTERNULL(@p0, "Age")

Example:

We have a filterbox p0 that contains a list of associations of the definition Person with different attributes (see image below), among these is the attribute Employment. What we want to do in this example is filter out those persons/rows with no Employment attribute value.

This is the default output we get if we simply get all the associations from p0 via the below expression. As you can see we also get persons with no chosen Employment (null) in the dataset.

In the below image we have used the FILTERNULL expression, which removes the rows/persons that have a null value in the Employment attribute. Notice that we now only receive 18 items compared to the previous 21 items, even though we still specified p0.all

$FILTERNULL({p0.all}, "Employment")

$FOREACH & $LOOP

Executes an expression for each item in a collection of items.

Note! $FOREACH and $LOOP does the same thing. So you can use either to receive the same result.

Input:

A collection of items. Each item can be of any type supported by the filter expression.

An expression to evaluate for each item.

The item can be referenced in the expression by {record} or {item}

Output:

A list of values created by the supplied expression.

Usage:

$FOREACH({p0},$get({record}, "value")*2)

$LOOP({p0},$get({record}, "value")*2)

Example 1:

In the below example we have a dataset (DS) containing associations of the definition Person with the attribute Scope.

We want to see what numbers we would get for each person if we add 20% to each person’s scope attribute value. We therefore add a foreach expression to a calculated column in the dataset:

$foreach({row}, $get({record}, "Scope")*1.2)

Dataset

Result in a Matrix

Example 2

We have a filter box p0 containing associations of the definition Persons with the attribute scope but let's double the scope of each person in p0. What we can do here is use the below expression as it will execute on each item in p0:

$LOOP({p0.all}, $get({record}, "Scope")*2)

Here is the actual age (top calculation box) compared to the loop result showing in the lower Calculation box:

$NTH

Returns the n:th object in a list or the n:th letter in a string.

Index is zero based so it will start at zero.

Input:

Collection/String, Integer

Output:

Any/String

Usage:

$NTH({p0}, 0) First object in p0.

$NTH("Hello", 1) "e".

Example:

In this example we want to see the 5th item in the p0 filter box. We therefore use the below expression. Notice that we used p0.all so the expression is executed on the whole list in p0. If we used simply p0 we would get the 5th item of the selected items in p0.

$NTH({p0.all}, 4)

Using p0 instead of p0.all

$PACK

Returns a list of the arguments supplied.

This function is useful for when you want to pack different attribute values together into one parameter.

Usage:

PACK("A","B","C"...)

$PACK($GET({p0},"Last Name"), $GET({p0},"Employment"))

Example 1:

Let's say we want to pack the 5th object in p0 with the 2nd object in p0. For this we can use the $NTH function with the $PACK function.

$PACK($NTH({p0.all}, 4), $NTH({p0.all}, 1))

This expression pack them both together into one output:

Example 2:

In this example we have a Base table in Knowledge Set Builder containing associations of the definition Person with attributes Employment and Age.

We want to add a calculated column to this dataset which should show both Employment and Age value in one cell for each person. We can for this use the below expression in a calculated column:

$TOSTRING($PACK({cell.Age}, {cell.Employment}))

This expression will pack both the current rows age and employment attribute value into one cell in the new calculated column in the Result table:

Example 3:

In the below image we have a dataset (DS) in Application Builder containing associations of the Definition Person with the attributes Age, Gender, Income and Scope.

We want to add two other columns which should show the percentage for each person’s income and scope according to the total income/scope for people of the same Gender and Age. So, if two people/rows have the same age and gender the percentage will show according to the total sum of these two incomes and scopes. To achieve this we can use the below expression in a result matrix:

$PERCENT({DS}, $PACK("Income","Scope"), "Gender", "Age")

Note that If we didn’t use $PACK in the expression, Inorigo would not be able to understand that we want both Income and Scope as Value columns.

Scope would instead be read as a total grouping column and the below expression would check if there were any persons with identical values in Scope, Age and Gender before returning the percentage of total income of these people/rows.

$PERCENT({DS}, "Income", "Scope", "Age", " Gender ")

See $PERCENT for more information about the usage this expression.

$SELECT

Select rows and columns from a Dataset or a List of Dataset rows.

This function allows you to use simple variable names for cells. (e.g {cell.age} instead of $GET({record},"age").

Note:

An empty filter parameter will make the function return all rows.
No columns parameter will make the function return all columns.
"*" as a column parameter will make the function return all columns.

Input:

Rows and columns from a Dataset or a List of Dataset rows.

Output:

A new dataset

Usage:

$SELECT({MyDataset}, {cell.age} > 21, "name","age").

$SELECT({MyDataset}, $GET({record},"age") > 21, "name","age").

$SELECT({MyDataset}, , "name","age").

$SELECT({MyDataset}, , $PACK("name","age")).

Example:

We have a dataset containing associations of the definition Person with attributes, Age, Scope, Gender, Income and Start Date.

Here is the default dataset DS tin a result matrix

But we want to only see the people with a Scope attribute value above 45 with the columns Person, Gender, and Scope in our result matrix. So, we add the below expressionto achieve this:

$SELECT({DS}, {cell.Scope} > 45, "Person", "Gender", "Scope")

$SIZE

Performs a size operation on a value.

Input:

Array / Collection / List / String.

Output:

The size as an integer.

Usage:

$SIZE({Dataset}) ->Size of a collection.

$SIZE("Hello") ->Size of a string.

$SIZE({p0}) ->number of selected items in a filter box

Example 1:

To check if any item is selected in p0 we can use the below expression. Note that it will be a Boolean expression since we add the > operator. So, we add the expression into a calculation box and it will return true or false depending on whether any object is selected in p0.

We are asking Inorigo if the size/number of the selected objects in p0 is larger than zero.

$Size({p0}) > 0

Not selected

Selected

Example 2:

Here we want to see how many in p0 that has the attribute value "Inpraxius" in the attribute Employment. We therefore use the SIZE function together with a GET and FILTER function.

This expression is telling Inorigo to filter the whole list in p0 (p0.all), to only output the rows that has an Employment equal to the string "Inpraxius" and then count the number of objects from the result with SIZE

$SIZE($FILTER({p0.all}, $GET({record}, "Employment") = "Inpraxius"))

$SORT

Performs sorting on a collection of items.

Input:

Items. A collection of items

Optional - Descending. Default is false (i.e ascending).

Optional - Case sensitive. The default is true.

Optional - Attributes for sorting instances on specified attributes.

Output:

Sorted list.

Usage:

$SORT({p0}, false, true)

$SORT({p0}, false, true, "Amount", "Category")

$SORT(@ListOfNumbers)

Example:

We have a dataset (DS) containing associations of the definition Person with the attributes Age, Gender, Employment and StartDate.

Default dataset "DS" in a Matrix

We want to sort this dataset from earliest start date to latest start date in a result matrix. We therefore use the expression below, which will sort the StartDate column in ascending order. Note also that we added true to the case sensitive option.

$SORT({DS}, false, true, "StartDate")

Result of our SORT Expression

$TOARRAY

Creates an array of the supplied input.

Some functions need their input in an array or list. This function can therefore be combined with these functions to convert the input into the correct data type.

No input or a null first input will return a zero-length array.
If one single input is iterable (collection/dataset/array), this will be converted into an array.

Input:

Any object(s).

Output:

Array of objects.

Usage:

$TOARRAY("a","b","c"...).

Example:

$SORT($TOARRAY("Aab", "aAc", "Aaa"), true, false) will get the result [aAc, Aab, Aaa]

$TOLIST

Creates a List of the supplied input.

Some functions need their input in an array or list. This function can therefore be combined with these functions to convert the input into the correct data type.

No input or a null first input will return a zero-length list.
If one single input is an array this will be converted into a list.
If one single input is a collection this will be returned as a list.

Input:

Value(s). Any object.

Output:

List of objects.

Usage:

$TOLIST("a","b","c"...).
``
$SORT($TOLIST("Aab", "aAc", "Aaa"), true, false) will get the result [aAc, Aab, Aaa]

$OBJECT

Returns an GlobalID using the supplied identification.

Input:

Global ID or Type and ID.

Output:

GlobalID.

Usage:

$OBJECT(@MyGID).
$OBJECT("CoProduct",@MyID).

$TODATE

Parse argument to timestamp.

Null argument is returned as null. Invalid arguments will cause function to return null.

Input:

Object (java.util.Date, Long or String), String (date pattern), String (locale), java.util.Date (default date), String (time zone)

Output:

DateTime

Usage:

$TODATE("10/13/2014", "mm/dd/yyyy")

$TODATE("2022-12-28")

Example:

Expression below will convert "2022-03-21" to a date and then add two days, resulting in the date 2022-03-23 as an output:

$ADDDAY($TODATE("2022-03-21"), 2)

$TODATETIME

Parse argument to timestamp.

Note! Null first argument will return now. Invalid arguments will cause function to return null.

Input:

String/DateTime

Output:

DateTime

Usage:

$TODATETIME("10/13/2014", "mm/dd/yyyy")

$TODOUBLE

Parses argument to double

Input:

String/Number or Collection of String/Number

Output:

Double

Usage:

$TODOUBLE(42) == 42.0
$TODOUBLE("34.5") == 34.5
$TODOUBLE(@MyNumericString)

$TOGLOBALID

Creates a GlobalID.

Usage:

$TOGLOBALID(Class [as string], UUID [as string])
$TOGLOBALID(GlobalID [as string])

$TOINT

Parse argument to integer

Input:

String/Number or Collection of String/Number

Output:

Integer

Usage:

$TOINT(42) == 42
$TOINT("34") == 34
$TOINT(@MyNumericString)

$TOLONG

Parse argument to long.

Long is a datatype similar to integer but can hold larger values.

Input:

String/Number or Collection of String/Number

Output:

Long

Usage:

$TOLONG(42) == 42
$TOLONG("34") == 34
$TOLONG(@MyNumericString)

$TOSTRING

Returns a string representing the object

Note! Null first argument will return empty string.

Input:

Any

Output:

String

Usage:

$TOSTRING(42.0) -> "42.0"

$TOSTRING(@MyGlobaID) -> Default presentation string for the identified entity.

Example:

In this example we have a Filterbox (p13) in Application Builder containing several Knowledge Sets. We want to be able to see the IDs for each of these so we add the expression below to a Calculation Box:

Result on web

$TOULONG

Parse argument to unsigned long (BigInteger)

Input:

String/Number or Collection of String/Number

Output:

Unsigned Long

Usage:

$TOULONG(42) == 42
$TOULONG("34") == 34
$TOULONG(@MyNumericString)

DateTime Functions

Whats is a DateTime Function?

These functions performs an action or calculation on a date and time value

$DATE_LONG

Formats a timestamp according to Datatimelayout DATE_LONG format: "EEE, d MMM yyyy"

Input:

Date to be formatted

Output:

String

Usage:

$DATE_LONG(@MyDate).

$DATE_SHORT

Formats a timestamp according to DataTimeLayout DATE_SHORT format: "yyyy-MM-dd"

Input:

Date to be formatted

Output:

String

Usage:

$DATE_SHORT(@MyDate).

$DATEADD

Adds to a given date depending on DatePart.

Note! Use a negative number to subtract. Only numeric DateParts are supported.

Input:

DateTime, DatePart, Integer

Output:

DateTime

Usage:

$DATEADD("HOUR", @MyDate, 36).

$DATEADD("WEEK", @MyDate, 4).

$ADDDAY(@NOW, 2) → Adds two days to todays date

$DATEFORMAT

Formats a timestamp as a string.

Note! Month is specified by capitalized MM and Minute by lowercase mm**
Input:**

Date to be formatted

Output:

String

Usage:

$DATEFORMAT(@MyDate, "DATE_SHORT").

$DATEFORMAT(@MyDate, "DATETIME_LONG").

$DATEFORMAT(@MyDate, "yyyy-MM-dd").

$DATEFORMAT($NOW(), "MM/dd yyyy").

$DATEFORMAT($NOW(), "MM/dd yyyy").

$DATEPART

Returns the desired date part of a timestamp.

Input:

DateTime, DatePart

Output:

Integer/String

Usage:

$DATEPART(@MyDate, "MONTH").

$DATEPART(@MyDate, "QUARTER").

$DATEPART(@MyDate, "YEAR_QUARTER").

$DATEPART($Now(),"Year") -> (e.g.) 2015

$DATETIME_LONG

Formats a timestamp according to DataTimeLayout DATETIME_LONG format: "EEE, d MMM yyyy HH:mm:ss"

Input:

Date to be formatted

Output:

String

Usage:

$DATETIME_LONG(@MyDate).

$DATETIME_LONG($NOW()).

$DATETIME_SHORT

Formats a timestamp according to DataTimeLayout DATETIME_SHORT format: "yyyy-MM-dd HH:mm:ss"

Input:

Date to be formatted

Output:

String

Usage:

$DATETIME_SHORT(@MyDate).

$DATETIME_SHORT($NOW()).

$DAY

Calculate the day of a date, or the current day if no date argument is passed.

Input:

DateTime

Output:

Integer

Usage:

$DAY(@MyDate)

$DAY()

$DAY(@NOW) → If today is 2023-05-30 it will output 30

$DAYNAME

Calculate the day name of a date, or the current day name if no date argument is passed.

Input:

DateTime

Output:

String

Usage:

$DAYNAME(@MyDate)

$DAYNAME()

$DAYNAME(@NOW) → will output for example "Tuesday", if today is a tuesday.

$HOUR

Calculate the hour of a date, or the current hour if no date argument is passed.

Input:

Datetime

Output:

Integer

Usage:

$HOUR(@MyDate)

$HOUR()

$HOUR(@NOW) → will output for example 10 if now is 10:00 o'clock

$MILLIESECOND

Calculate the millisecond of a date, or the current millisecond if no date argument is passed.

Input:

DateTime

Output:

Integer

Usage:

$MILLISECOND(@DATE)

$MILLISECOND()

$MINUTE

Calculate the minute of a date, or the current minute if no date argument is passed.

Input:

DateTime

Output:

Integer

Usage:

$MINUTE(@MyDate)

$MINUTE() → will for example output 3 if the current time is 10:03

$MONTH

Calculate the month of a date, or the current month if no date argument is passed.

Note! Index is zero based so it will start at zero: January = 0

Input:

DateTime

Output:

Integer

Usage:

$MONTH(@MyDate)

$MONTH()

$MONTH($TODATE("2023-12-12")) → 11

$MONTHNAME

Calculate the month name of a date, or the current month name if no date argument is passed.

Input:

DateTime

Output:

String

Usage:

$MONTHNAME(@MyDate)

$MONTHNAME()

$MONTHNAME() -> (e.g.) June

$NOW

Calculates the current date and time.

Usage:

$NOW()

$QUARTER

Calculate the quarter of a date, or the current quarter if no date argument is passed.

Input:

DateTime

Output:

Integer

Usage:

$QUARTER(@MyDate)

$QUARTER()

$QUARTER($TODATE("2023-12-12")) → 4

$QUARTERNAME

Calculate the quarter name of a date, or the current quarter name if no date argument is passed.

Input:

DateTime

Output:

String

Usage:

$QUARTERNAME(@MyDate)

$QUARTERNAME() → eg. Q2

$SECOND

Calculate the second of a date, or the current second if no date argument is passed.

Input:

DateTime

Output:

Integer

Usage:

$SECOND(@MyDate)

$SECOND()

$TIME_SHORT

Formats a timestamp according toDateTimeLayout - Time Short

Usage:

$TIME_SHORT(@MyDate).

$TIME_SHORT($NOW()). eg. 13:19:14

$TIME_SHORT_MILLIES

Formats a timestamp according to DateTimeLayout - TIME_SHORT_MILLIES format: "HH:mm:ss.SSS"

Usage:

$TIME_SHORT_MILLIES(@MyDate).

$TIME_SHORT_MILLIES(@NOW). eg. 13:20:06.518

$TIMESPAN

Calculates the timespan between two timestamps or a duration.

Input:

DateTime date1, DateTime date2, String Datepart

Output:

Timespan/Long

Usage:

$TIMESPAN(@MyDate1, @MyDate2) returns a Timespan object, useful for output like "8h 5m 34s".

$TIMESPAN(date1, date2, "WEEK")

An example using dataset: $LOOP({DATASET},[$timespan($get({record},"p37"),$get({record},"p38"), "WEEK")])

Example:

The below expression will return the time between 2023-01-08 and 2023-02-09:

$TIMESPAN($TODATE("2023-01-08"), $TODATE("2023-02-09"))

$WEEK

Calculate the week of a date, or the current week if no date argument is passed.

Input:

DateTime

Output:

Integer

Usage:

$WEEK(@MyDate)

$WEEK()

$WEEK($TODATE("2023-12-12")) → 50

$YEAR

Calculate the year of a date, or the current year if no date argument is passed.

Input:

DateTime

Output:

Integer

Usage:

$YEAR(@MyDate)

$YEAR()

$YEAR($TODATE("2023-12-12")) → 2023

$YEAR_QUARTER

Calculate the year and quarter name of a date, or the current year and quarter name if no date argument is passed.

Input:

DateTime

Output:

String

Usage:

$YEAR_QUARTER(@MyDate)

$YEAR_QUARTER() eg. 2023 - Q2

DEPRECATED FUNCTIONS

Whats is a Deprecated Function?

These functions is no longer relevant and users are discouraged to use these functions.

$LOAD

Returns an entity using the supplied identification.

Input:

Global ID or Type and ID.

Output:

GlobalID.

Usage:

$LOAD(@MyGID).

$LOAD("CoProduct",@MyID).

ENTITY FUNCTIONS

$GET

Returns an Attribute Value. Can also be used to get a hash map value.

The index parameter is optional

Input:**

DataObject,String[, Integer]

Output:**

Any

Usage:**

$GET(entity, attribute name [, index])

$GET(entity, attribute id [, index])

Example:

In this example we have a filter box (p0) containing instances of Person with the attributes First Name and Last Name. We want to get the Last name in a calculation box. We therefore use the below expression:

$GET({p0}, "First Name")

LOGIC FUNCTIONS

What is a logic function?

These functions are used to manipulate and evaluate boolean values or conditions.

$CASE

Returns the first argument following a boolean true argument.

Input:

condition, val, condition2, val2, ...

Output:

Any

Usage:

$CASE(false, 42, true, 12) -> 12

$CASE(true, 42, true, 12) -> 42

$CASE(false, 42, false, 12) -> null

Example 1:

In the below example we have created a dataset which consists of the Association definition Person with the attributes Employment and Scope. We want to check the people in the dataset have a high scope (equal or above 50) or a low scope (lower than 50), we therefore create a calculated column in our dataset with the expression:

$CASE({Scope}>=50, "High Scope")

Adding the DS dataset in a matrix will then result in below image when opening on the web. Showing the Person associations.

This is a very basic demonstration of the CASE function works. Which is similar to that of the IIF function, with the difference that we only supply a value for a true boolean argument.

Example 2:

You can do simple expressions with the Case function as above but what $CASE is truly useful for is longer more complex case expressions. Such as below:

$CASE({Scope} > 20 && {Employment} = "Fermipar", "High scope [Fer]", {Scope} > 40 && {Employment} = "Inpraxius", "High Scope [Inp]", {Scope} > 60 && {Employment} = "Reneweng", "High Scope [Ren]")

What the function will do here is first check if the first condition is true, is Scope value higher than 20 and is the employment equal to Fermipar. If true then return the first argument, which is the string "High Scope [Fer]".
If the first condition is not true, it will check if the second condition is true, is Scope value higher than 40 and is the employment equal to Inpraxius. If this second condition is true it will return the second argument, which is the string "High Scope [Inp]".
But if the second condition is also not true it will then check the third condition and so on.

So you can create a very long case expression which will check a large number of conditions and return your desired argument for each condition (if true). But if the condition is false it will check the next condition.

If we now put this more complex expression containing the $CASE Function into the same dataset and calculated column as the first example, we will get the below view instead.

$IFNULL

Returns second or third argument, depending on whether first argument is null.

Input:

Expression to test whether null.
value if null
value if not null.

Output:

The value specified depending on if expression is null or not.

Usage:

$IFNULL(@NULL, 42, 12) -> 42

$IFNULL("yxa", 42, 12) -> 12

Example:

In the below example we have created a dataset in Knowledge Builder which consists of the Association Definition Person with attributes Employment and Scope. But as you can see some people are missing Scope Attribute values in the list:

We want to check if we are missing any data in the attribute “Scope” in the dataset, so we add a calculated column and add our expression:

$IFNULL({Scope}, "Missing", "")

This expression will check each row in the dataset to see if there are any null values (empty) in the “Scope” attribute, if so, it will add a string called “Missing” and if any other value is found it will return blank (“”).

But lets say we want the "missing" text in the same column as the Scope attribute values. So if person has a value it will show the scope value but if null it will return "missing" in the cell.

What we can do here is edit our calculated column and change the expression to the below. This will check if any row in the Scope column is null, and if true it will return "missing" but if false it will return the Scope attribute value. Notice also that we convert the if false argument to a string as we have the data type as String.

$IFNULL({Scope}, "Missing", $TOSTRING({cell.Scope}))

Now we click okay and Run the program. Which will result in the below view with all the info in one column:

$IIF

Returns second or third argument, depending on boolean first argument.

Input:

condition
value if true
value if false

Output:

Any

Usage:

$IIF(true, 42, 12) -> 42
\ `$IIF(false, 42, 12) -> 12`\
$IIF($EXPR("50 > 12"), 42, 12) -> 42

Example 1

In the below example we have created a dataset in Application Builder which consists of the definition Person with the attributes Employment and Age.

We want to quickly check if we have any persons older than 45 in the dataset, so we add a calculated column in our dataset and add our below expression:

$IIF({Age} > 45, "Yes, older", " ")

This expression will check if there are any rows with a value larger than 45 in the “Age” attribute, if so, it will add a string called “Yes, older” in the calculated column. If any other value is found it will return blank.

Lastly, we open the DS dataset in a result matrix:

Example 2

We have a filter box containing the associations of the definition Person with the attributes Employment and Start Date. We want to check if the latest employed person have been employed for 2 years or more. To check this, we can add a the below expression to a calculation box:

$IIF($MAX($get({p0.all},"StartDate"))>=$AddYear($NOW(), -2), "Less than two years", "More than 2 years")

Step by step explanation:

*This expression will get the max value on the attribute StartDate (latest date) from all items in the p0 list.
$MAX($GET({p0.all},"StartDate"))*
*Then compare if it’s equal or later than 2 years ago
>=$AddYear($NOW(), -2)*
*If true the expression will output “Less than 2 years”, and if false it will output “More than 2 years”.
$IIF($MAX($get({p0.all},"StartDate"))>=$AddYear($NOW(), -2), "Less than two years", "More than 2 years")*

Result:

Extra: Initial Filter for item with latest start date

If we want to add a selection filter to p0 that will also select the Person with the latest start date in p0, we can add the below expression to the "Initial Selection" expression text box for p0:

{record} = $FILTER({p0.all},$GET({record}, "StartDate") = $MAX($GET({p0.all}, "StartDate")))

$ISNULL

Returns wether first argument is null.

The $ISNULL function works similar to the $IFNULL function with the difference that ISNULL is strictly boolean that will return either true or false.

Input:

Expression to test if null.

Output:

True or false depending on if expression is null or not.

Usage:

$ISNULL(@NULL) -> true
$ISNULL("yxa") -> false

Example 1:

Working with a Dataset containing the Associations of the Definition Person with the attributes Employment and Scope in Knowledge Builder, we want a new calculated column containing true or false depending on whether the same row has any value in the Scope column:

$ISNULL({Scope})

This is the result of the expression in a calculated column:

It can be useful to use this function as an argument in anther function such as $IIF, as $IIF requires a boolean argument as an input. So, if Scope is null/true then return something else.

Example 2:

In this example we have the same dataset with the definition Person and attributes Employment and Scope. But now we want to check if the Scope value is null and if the employment is equal to Fermipar, as these are the only ones that actually required Scope values. What we can do here is is the below expression:

$IIF($ISNULL({Scope}) && {Employment} == "Fermipar", "Part of Fermipar. Missing Scope", " ")

This expression will check if the scope value is empty and employment is equal to Fermipar, if true it will return the string "Part of Fermipar. Missing Scope", and if false it will return nothing " ". Resulting in the below result table view:

Overview​

Where can we use expressions?​

How to use expressions​

Vocabulary​

$ Functions - Summary List​

Legacy Expression Editor​

Refresh this menu​

Expression Reference Guide​

Advanced Functions​

$CALL​

$Coalesce​

$EXPR​

$IS_AUTHORIZED​

$KNOWLEDGE_SET​

$Null​

$SERIAL_NO​

$TO_COORDINATE​

$VELOCITY_IFRAME​

$VELOCITY_URL​

Aggregation functions​

$﻿AGGREGATE​

$AVG​

$MAX​

$MIN​

$PERCENT​

$SUM​

Collection Functions

$CONTAINS​

$DISTINCT​

$DISTINCTBYCOLUMNS​

$FILTER​

$FILTERNULL​

$FOREACH & $LOOP​

$NTH​

$PACK​

$SELECT​

$SIZE​

$SORT​

$TOARRAY​

$TOLIST​

$OBJECT​

$TODATE​

$TODATETIME​

$TODOUBLE​

$TOGLOBALID​

$TOINT​

$TOLONG​

$TOSTRING​

$TOULONG​

DateTime Functions​

$DATE_LONG​

$DATE_SHORT​

$DATEADD​

$DATEFORMAT​

$DATEPART​

$DATETIME_LONG​

$DATETIME_SHORT​

$DAY​

$DAYNAME​

$HOUR​

$MILLIESECOND​

$MINUTE​

$MONTH​

$MONTHNAME​

$NOW​

$QUARTER​

$QUARTERNAME​

$SECOND​

$TIME_SHORT​

$TIME_SHORT_MILLIES​

$TIMESPAN​

$WEEK​

$YEAR​

$YEAR_QUARTER​

DEPRECATED FUNCTIONS​

$LOAD​

ENTITY FUNCTIONS​

$GET​

LOGIC FUNCTIONS​

$CASE​

Overview

Where can we use expressions?

How to use expressions

Vocabulary

$ Functions - Summary List

Legacy Expression Editor

Refresh this menu

Expression Reference Guide

Advanced Functions

$CALL

$Coalesce

$EXPR

$IS_AUTHORIZED

$KNOWLEDGE_SET

$Null

$SERIAL_NO

$TO_COORDINATE

$VELOCITY_IFRAME

$VELOCITY_URL

Aggregation functions

$AGGREGATE

$AVG

$MAX

$MIN

$PERCENT

$SUM

$CONTAINS

$DISTINCT

$DISTINCTBYCOLUMNS

$FILTER

$FILTERNULL

$FOREACH & $LOOP

$NTH

$PACK

$SELECT

$SIZE

$SORT

$TOARRAY

$TOLIST

$OBJECT

$TODATE

$TODATETIME

$TODOUBLE

$TOGLOBALID

$TOINT

$TOLONG

$TOSTRING

$TOULONG

DateTime Functions

$DATE_LONG

$DATE_SHORT

$DATEADD

$DATEFORMAT

$DATEPART

$DATETIME_LONG

$DATETIME_SHORT

$DAY

$DAYNAME

$HOUR

$MILLIESECOND

$MINUTE

$MONTH

$MONTHNAME

$NOW

$QUARTER

$QUARTERNAME

$SECOND

$TIME_SHORT

$TIME_SHORT_MILLIES

$TIMESPAN

$WEEK

$YEAR

$YEAR_QUARTER

DEPRECATED FUNCTIONS

$LOAD

ENTITY FUNCTIONS

$GET

LOGIC FUNCTIONS

$CASE

$IFNULL