Expressions

Expression fields are derived data created using the Built-In functions provided with Engine. This section introduces Expression fields and looks at some of the common uses of expression based derived data.

Expression fields are commonly used in conjunction with aggregate fields and remaps to execute both simple and complex data engineering within Engine. They can be created both "on the fly" via the campaign and analytics tools, or as part of a regular load process.

An Expression column can be anything from a simple concatenation of string values to a complex mathematical calculation and provides a flexible mechanism for manipulating and transforming data into almost any desired format.

Engine's underlying CBAT technology lends itself well to the calculation of all derived data, and as a consequence, Expression fields are a key feature of Engine.

Creating an expression field

iLoader

Alterian's Script Based ETL tool, iLoader, provides direct support for creating expression columns through the EXPRESSION keyword.

By using iLoader scripts, it is possible to automatically create expression based columns at the end of a data load. This allows the same expressions to be recreated each time the data is refreshed.

Built In Functions

There is a wide variety of built-in expression functions supplied with Engine, including:

String Functions: INSTR, LEFT, RIGHT, UCASE, LCASE, STRIP, TRIM
Conversion Functions : INT, BIGINT, REAL, STRING, WSTRING, DATE, TIME
Date Functions : AGE, DAYAT, MONTH, YEAR, DAYSTO
Special Functions : IURN, MAXIMUM, MINIMUM, MID

Uses of Expressions

Expression fields are extremely versatile and are used in a wide variety of data engineering operations. Some common uses are:

Creating scores and models
Tidying up data
Adding more information to existing data
Reformatting data
Data conversion

Operational Elements of an Expression

Dynamic Field	Owner Table	Table on which the expression is created.
	Source Table(s)	The owner table(s) of the Expression Field(s). Note that an expression may have multiple source tables, unlike an aggregate which will only ever have one.
	Expression Field(s)	Fields used in the expression. Must be linked to the Owner Table of the expression.

Concatenating Strings

To concatenate strings in expressions, use the + symbol. For example, the following expression creates a new column of unique forenames and surnames:

[Demo].[Cust].[Surname] + "_" + [Demo].[Forename]

	Online & Instructor-Led Courses \| Training Videos \| Webinar Recordings
	© Alterian. All Rights Reserved. \| Privacy Policy \| Legal Notice