Campaign Manager - Campaign Manager (Silverlight)

Engine Expressions

The following expressions are supported for use with the Data Engineering Expression tool:

ABS

Removes the sign from each record in the column.

Part Description
[RESULT] Data Type = INTEGER_FIELD or REAL_FIELD
[COLUMN] Data Type = INTEGER_FIELD or REAL_FIELD

AGE

Returns the number of years between two dates.

[RESULT] = AGE([DATEFIELD1] , [DATEFIELD2])

Part Description
[RESULT] Data Type = INTEGER_FIELD Age of [DATEFIELD1] at [DATEFIELD2]
[DATEFIELD1] Data Type = DATE_FIELD or DATETIME_FIELD
  • [FIELDNAME] of Field to get age for
[DATEFIELD2] Date at which to get the age. Can be either of the following:
  • [FIELDNAME] of data type DATE_FIELD or DATETIME_FIELD.
  • String Date in format determined by ISession.SystemDateFormat or ISession.SystemDateTimeFormat, for example "21032004" if SystemDateFormat = 2 (DDMMCCYY) and both parameters are of data type DATE_FIELD
Note: Both [DATEFIELD] parameters must be of the same data type, either DATE_FIELD or DATETIME_FIELD. Functions DATE and DATETIME can be used for data type conversion.

ALLMONTHS

Returns the year and month parts of a date field, in the format CCYYMM (for example 199805 would be returned where the specified datefield contained a date which fell in May 1998).

[RESULT] = ALLMONTHS([DATEFIELD])
Part Description
[RESULT] Data Type = INTEGER_FIELD Year and month parts of [DATEFIELD]
[DATEFIELD] Data Type = DATE_FIELD, DATETIME_FIELD

ALLQUARTERS

Returns the year and quarter of a date field, in the format CCYYQQ (for example, 199802 would be returned where the specified datefield contained a date which fell in the second quarter of 1998).

[RESULT] = ALLQUARTERS([DATEFIELD])
Part Description
[RESULT] Data Type = INTEGER_FIELD Year and quarter of [DATEFIELD]
[DATEFIELD] Data Type = DATE_FIELD, DATETIME_FIELD

AVG

Returns the average value of the column data.

[RESULT] = Avg([FIELDNAME])
Part Description
[RESULT] Data Type = INTEGER_FIELD, REAL_FIELD Average value of specified field.
[FIELDNAME] Data Type = INTEGER_FIELD, REAL_FIELD The field from which to obtain the average.

CEIL

Returns the smallest integer greater than or equal to a value.

[RESULT] = CEIL([NUMERICFIELD])
Part Description
[RESULT] Data Type = INTEGER_FIELD
[NUMERICFIELD] Data Type = INTEGER_FIELD, REAL_FIELD

CHR

Returns the ASCII character represented by a numeric column (mod 256).

[RESULT] = CHR([NUMERICFIELD])
Part Description
[RESULT] Data Type = TEXT_FIELD
[NUMERICFIELD] Data Type = REAL_FIELD, INTEGER_FIELD

CODE

Returns the ASCII code of the first character.

[RESULT] = CODE([FIELDNAME])
Part Description
[RESULT] Data Type = TEXT_FIELD
[FIELD] Data Type = TEXT_FIELD, INTEGER_FIELD, REAL_FIELD

CONTAINSALL

Returns all records where ALL of the tokens specified are present.

[RESULT] = ContainsAll([FIELDNAME])[OPERATOR][VALUE]
Part Description
[RESULT] Domain containing all valid records
[FIELDNAME] Name of an ARRAY FIELD (TEXT_FIELD where Format = VARRAY or FARRAY)
[OPERATOR] IN, =
[VALUE] Data Type = String
  • If not decoded, [VALUE] is one or more token values, for example "12" or "2","4"
  • If decoded, [VALUE] is the decoded value, for example "UK"
  • If more than one value is specified, the IN operator must be used, for example IN "UK","FRANCE"
  • "12" is the same as "21" — that is, the segment order is irrelevant in terms of returning the correct values

CONTAINSEXACTLY

Returns all records where the tokens contained match only those specified.

[RESULT] = ContainsExactly([FIELDNAME])[OPERATOR][VALUE]
Part Description
[RESULT] Domain containing all valid records
[FIELDNAME] Name of an ARRAY FIELD (TEXT_FIELD where Format = VARRAY or FARRAY)
[OPERATOR] IN, =
[VALUE] Data Type = String
  • If not decoded, [VALUE] is one or more token values, for example "12" or "2","4"
  • If decoded, [VALUE] is the decoded value, for example "UK"
  • If more than one value is specified the IN operator must be used, for example IN "UK","FRANCE"
  • "12" is the same as "21" — for example, segment order is irrelevant in terms of returning the correct values

COUNT

Returns the number of records or values on the column.

[RESULT] = Count([VALUE])[EXPRESSION]
Part Description
[RESULT] Data Type = INTEGER_FIELD Number of records or values. Note that count does NOT return columns for viewing.
[VALUE] String
  • * : Wildcard, will return a count of all rows returned by [EXPRESSION]
  • [FIELDNAME] : Equivalent to : Count(*) WHERE [FIELDNAME] IS NOT NULL
[EXPRESSION] Any valid FROM or WHERE statement. for example FROM [CUSTOMER] WHERE [AGE]=22
Note: Note Main
Note: It is not possible to list more than one FIELD_NAME within the COUNT statement.

COUNTTOKENS

Returns all records where the number of tokens matches the specified value.

[RESULT] = CountTokens([FIELDNAME])[OPERATOR][VALUE]
Part Description
[RESULT] Domain containing all valid records
[FIELDNAME] Name of an ARRAY FIELD (TEXT_FIELD where Format = VARRAY or FARRAY)
[OPERATOR] GT, GE , LT , LE , EQ , BETWEEN , XBETWEEN , NE
[VALUE] Data Type = Long Number of tokens If [VALUE] = 0, will return all records where no segments were specified. This is the same as using Is NULL.

CUMUL

Returns the cumulative value of all column records or the running balance .

[RESULT] = CUMUL([FIELDNAME)]
Part Description
[RESULT] Data Type = REAL_FIELD
[FIELD] Data Type = INTEGER_FIELD, REAL_FIELD

DATE

Returns the input data converted from formatted text to a DATE data type value. Used in an expression to force the creation of a DATE_FIELD.

[RESULT] = DATE([VALUE],[FORMAT])
Part Description
[RESULT] Data Type = DATE_FIELD
[VALUE] Input parameter can be any of the following:
  • Integer: If [VALUE] is an integer it is assumed that it refers to an INTERNAL DATE VALUE
  • Column: The column must contain formatted DATE or DATETIME data as Strings
[FORMAT] Optional. Format of [VALUE]. If omitted, then [VALUE] must be provided in a format matching the SystemDateFormat.

If the FORMAT is specified, it must use the following codes:

  • CC = Century
  • YY = Year
  • MM = Month Numbers
  • MMM = 3 character months, such as "Jan", "Feb", "Jul" etc
  • DD = Day
for example DATE([Demo].[Table].[Date1], "CCYYDDMM")

Note: Regardless of the INPUT format (as specified by [FORMAT]) the data will be stored in the format specified by SystemDateFormat. It is this format that must be used when evaluating the DATE in expressions or queries.

DATETIME

Returns a DATETIME_FIELD representation of the input data. Used in an expression to force the creation of a DATETIME_FIELD. Supports 2 types of syntax.

Syntax 1:

[RESULT] = DATETIME([VALUE1])

Part Description
[RESULT] Data Type = DATETIME_FIELD
  • Returns NULL if [VALUE] cannot be converted to DATETIME format.
  • The Format of the RESULT is determined by the SystemDateTimeFormat.
[VALUE]
  • Column containing formatted DATETIME data as string. Date must be in format of SystemDateTimeFormat
  • DATETIME literal in SystemDateTimeFormat.

Syntax 2:

[RESULT] = DATETIME([VALUE1],[VALUE2])

Part Description
[RESULT] Data Type = DATETIME_FIELD
  • Returns NULL if [VALUE] cannot be converted to DATETIME format.
  • The Format of the RESULT is determined by the SystemDateTimeFormat.
[VALUE1]
  • Column containing DATE data.
  • DATE literal in SystemDateFormat.
[VALUE2]
  • Column containing TIME data
  • TIME literal in SystemTimeFormat
Note: If no Date value is specified, or if the Date Value is NULL, then a base year of 1900 is used. If the TIME value is NULL or not specified then Midnight (00:00:00) will be used.

DAY

Returns the day portion of each record (1-31).

[RESULT] = DAY([DATECOLUMN])
Part Description
[RESULT] Data Type = INTEGER_FIELD
[DATECOLUMN] Data Type = DATE_FIELD, DATETIME_FIELD

DAYAT

Adds or subtracts (with minus sign) [integer] number of days to/from a date field.

[RESULT] = DAYAT([DATEFIELD],integer)
Part Description
[RESULT] Data Type = DATE_FIELD, DATETIME_FIELD
[DATEFIELD] Data Type = DATE_FIELD, DATETIME_FIELD

DAYSTO

Returns the number of days between 2 dates.

[RESULT] = DAYSTO([PARAM1],[PARAM2])
Part Description
[RESULT] Data Type = INTEGER_FIELD Number of days between [PARAM1] and [PARAM2]
[PARAM1] Data Type = DATE_FIELD or String
  • [FIELDNAME] of Date Field for first date
  • String Date in format determined by ISession.SystemDateFormat or ISession.SystemDateTimeFormat, for example "21032004" if SystemDateFormat = 2 (DDMMCCYY) and parameters are DATE_FIELD data type
[PARAM2] Data Type = DATE_FIELD or String
  • [FIELDNAME] of Date Field for second date
  • String Date in format determined by ISession.SystemDateFormat or ISession.SystemDateTimeFormat, for example "21032004" if SystemDateFormat = 2 (DDMMCCYY) and parameters are DATE_FIELD data type
Note:Both parameters must be of the same data type, either DATE_FIELD or DATETIME_FIELD. Functions DATE and DATETIME can be used for data type conversion.

DISTANCE

Calculates the distance between two geographic points.

[RESULT] = DISTANCE([FIELDNAME],[Value])
Part Description
[RESULT] Data Type = REAL_FIELD
[FIELDNAME] Data Type = TEXT_FIELD, INTEGER_FIELD, REAL_FIELD
[Value] Data Type = Same as [FIELDNAME] Must be a geographic value that exists in [FIELDNAME], for example a postcode.
Note: Requires Mapping configuration settings in Cerberus.Ini.

DISTINCT

Returns the record position of each value in a column's sorted list of distinct values.

[RESULT] = DISTINCT([FIELDNAME])
Part Description
[RESULT] Data Type = INTEGER_FIELD
[FIELDNAME] Data Type = Any

EXP

Returns the exponential of each column record. The column must hold numeric values.

[RESULT] = EXP([NUMERIC_COLUMN])
Part Description
[RESULT] Data Type = REAL_FIELD
[NUMERIC_COLUMN] Data Type = INTEGER_FIELD, REAL_FIELD

FIRSTQUARTILE

Returns the value at the first quartile of the column.

[RESULT] = FIRSTQUARTILE([NUMERIC_COLUMN])
Part Description
[RESULT] Data Type = REAL_FIELD
[NUMERIC_COLUMN] Data Type = INTEGER_FIELD, REAL_FIELD

FLOOR

Returns the largest integer less than or equal to each record.

[RESULT] = FLOOR([COLUMN])
Part Description
[RESULT] Data Type = INTEGER_FIELD
[COLUMN] Data Type = REAL_FIELD, INTEGER_FIELD

HOUR

Returns the hours part of a datetime or time column, in 24-hour format.

[RESULT] = HOUR([DATETIME_COLUMN])
Part Description
[RESULT] Data Type = INTEGER_FIELD
[DATETIME_COLUMN] Data Type = DATETIME_FIELD, TIME_FIELD

INSTR

Returns the numeric position in a column of the characters specified by a string.

[RESULT] = INSTR([COLUMN],[STRING])
Part Description
[RESULT] Data Type = INTEGER_FIELD The position at which the first character of [STRING] appears within [COLUMN].
[COLUMN] Data Type = TEXT_FIELD The field to search in.
[STRING] Data Type = String The string to search for.
Note:The position of the first character of [COLUMN] is 1. If [STRING] is not found, 0 is returned. The search is case sensitive.

INT

Returns the integer or whole number part of the input data. The number will be rounded to a whole number, eg: 1.7 will be returned as 2. Used in an expression to force the creation of an INTEGER_FIELD.

[RESULT] = INT([VALUE])
Part Description
[RESULT] Data Type = INTEGER_FIELD
[VALUE] Input parameter can be any of the following:
  • String Literal: Converts the String to an INTEGER
  • Column contains String Data
  • Numeric Literal: Rounds up the number to the nearest INTEGER
Note:Numbers are rounded to the nearest whole numbers. For example, 1.7 will be rounded up to 2, 1.3 will be rounded down to 1.0 and 1.5 will be rounded up to 2. If a column contains a value that cannot be converted into an integer, the value will be stored as NULL or 0.

ISNULL

Returns 0 where the column holds null, and 1 where the value is not null.

[RESULT] = ISNULL([COLUMN])
Part Description
[RESULT] Data Type = INTEGER_FIELD
[COLUMN] Data Type = TEXT_FIELD, INTEGER_FIELD, REAL_FIELD, DATE_FIELD, DATETIME_FIELD, TIME_FIELD

IURN

Returns the Internal Engine URN for a record.

[RESULT] = IURN([VALUE])[EXPRESSION]
Part Description
[RESULT] Data Type = INTEGER_FIELD Internal URN for each record.
[VALUE] String
  • [FIELDNAME] : Name or NameEx of primary key on table
[EXPRESSION] Any valid FROM or WHERE statement. Must include the owner table of the Field specified in [VALUE] for example SELECT IURN([Demo].[Customer].[FID]) FROM [Customer];
Note:Every record in every table has an internal URN.Engine URNs are 0 based. When used in the WHERE clause of a query, must use EXPR{} syntax.

KURTOSIS

Returns a value indicating the kurtosis of a column.

[RESULT] = KURTOSIS([COLUMN])
Part Description
[RESULT] Data Type = REAL_FIELD
[COLUMN] Data Type = INTEGER_FIELD, REAL_FIELD

LCASE

Returns the values of a text column in all lower case.

[RESULT] = LCASE([COLUMN])
Part Description
[RESULT] Data Type = TEXT_FIELD
[COLUMN] Data Type = TEXT_FIELD

LEFT

Returns a specified number of characters from the left of a text column.

[RESULT] = LEFT([COLUMN],[NUMBER])
Part Description
[RESULT] Data Type = TEXT_FIELD
[COLUMN] Data Type = TEXT_FIELD, UNICODE_FIELD The column from which to return a substring.
[NUMBER] Data Type = Integer The number of characters to return.

LEN

Returns the length of each record in the column.

[RESULT] = LEN([COLUMN])
Part Description
[RESULT] Data Type = INTEGER_FIELD
[COLUMN] Data Type = TEXT_FIELD, INTEGER_FIELD, REAL_FIELD, UNICODE_FIELD

LOG

Returns the natural logarithm of the column records.

[RESULT] = LOG([COLUMN])
Part Description
[RESULT] Data Type = REAL_FIELD
[COLUMN] Data Type = INTEGER_FIELD, REAL_FIELD

LOG10

Returns the base 10 logarithm of the column records.

[RESULT] = LOG10([COLUMN])
Part Description
[RESULT] Data Type = REAL_FIELD
[COLUMN] Data Type = INTEGER_FIELD, REAL_FIELD

LTRIM

Returns the values of a text column after removing any spaces to the left of the column.

[RESULT] = LTRIM([COLUMN])
Part Description
[RESULT] Data Type = TEXT_FIELD, UNICODE_FIELD
[COLUMN] Data Type = TEXT_FIELD, UNICODE_FIELD

MAX

Returns the maximum value of the column data.

[RESULT] = MAX([COLUMN])
Part Description
[RESULT] Data Type = DATE_FIELD, TIME_FIELD, DATETIME_FIELD, INTEGER_FIELD, REAL_FIELD Maximum value of specified field.
[FIELDNAME] Data Type = DATE_FIELD, TIME_FIELD, DATETIME_FIELD, INTEGER_FIELD, REAL_FIELD The field from which to obtain the maximum.

MAXIMUM

Returns whichever is the greater of two values.

[RESULT] = MAXIMUM([COLUMN],[COLUMN])
Part Description
[RESULT] Data Type = Same as [COLUMN]
[COLUMN] Data Type = Any
[COLUMN] Data Type = Any

MEAN

Returns the average value of the column.

[RESULT] = Mean([FIELDNAME)]
Part Description
[RESULT] Data Type = REAL_FIELD Average value of specified field.
[FIELDNAME] Data Type = INTEGER_FIELD, REAL_FIELD The field to obtain the average of.

MEDIAN

Returns the middle value of the column.

[RESULT] = MEDIAN([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

MID

Returns a substring from each record of the column.

[RESULT] = MID([COLUMN],[NUMBER],[NUMBER])
Part Description
[RESULT] Data Type = Same as [COLUMN] A substring of [COLUMN].
[COLUMN] Data Type = TEXT_FIELD, UNICODE_FIELD The string column from which to obtain a substring.
[NUMBER] Data Type = INTEGER The start position in the string to return characters from, 1-based. If the start position is greater than the number of characters in the string, a zero-length string is returned.
[NUMBER] Data Type = INTEGER The number of characters to return from the string. If this is greater than the length of the string, all characters from the start position to the end of the string are returned.

MIN

Returns the minimum value of the column.

[RESULT] = Min([FIELDNAME])
Part Description
[RESULT] Data Type = Matches that of [FIELDNAME] column Minimum value of specified field.
[FIELDNAME] Data Type = DATE_FIELD, TIME_FIELD, DATETIME_FIELD, INTEGER_FIELD, REAL_FIELD The field from which to obtain the minimum.

MINIMUM

Returns whichever is the lower of two values.

[RESULT] = MINIMUM([COLUMN],[COLUMN])
Part Description
[RESULT] Data Type = Same as [COLUMN]
[COLUMN] Data Type = Any
[COLUMN] Data Type = Any

MINUTE

Returns the minutes part of a datetime or time column.

[RESULT] = MINUTE([DATETIME_COLUMN])
Part Description
[RESULT] Data Type = INTEGER_FIELD
[DATETIME_COLUMN] Data Type = DATETIME_FIELD, TIME_FIELD

MODE

Returns the value that occurs most often in a column.

[RESULT] = MODE([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

MONTH

Returns the month portion of each record (1-12).

[RESULT] = MONTH([DATE_COLUMN])
Part Description
[RESULT] Data Type =
[DATE_COLUMN] Data Type =

NORMALISED

Returns the normalized value of each record in the column, defined as the difference between each value and the average value, divided by the standard deviation.

[RESULT] = NORMALISED([COLUMN])
Part Description
[RESULT] Data Type = REAL_FIELD
[COLUMN] Data Type = INTEGER_FIELD, REAL_FIELD

PAREA

Returns the Postal Area from a UK-Style postcode. The postal area is the first 2 letters, or the first letter if the 2nd character is a number. Parameter can be a string or a column containing postcodes.

[RESULT] = PAREA([PARAM])
Part Description
[RESULT] Data Type =
[PARAM] Data Type =

PDIST

Returns the Postal District from a UK-Style postcode. The Postal District is the postcode minus the last three chars, with all spaces removed. Parameter can be a string or a column containing postcodes.

[RESULT] = PDIST([PARAM])
Part Description
[RESULT] Data Type =
[PARAM] Data Type =

POWER

Returns each column record raised to the power of [number]

[RESULT] = POWER([COLUMN],[NUMBER])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =
[NUMBER] Data Type =

PROPER

Returns a column's data in initial capitals (i.e lower case with a leading capital).

[RESULT] = PROPER([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

PROPERSENTENCE

Returns a columns data in intitial capitals for each word in the sentence (i.e lower case with a leading capital)

[RESULT] = PROPERSENTENCE([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

PSECT

Returns the Postal Sector from a UK-Style postcode. The Postal Sector is the postcode minus the last two chars, with all spaces removed. The string [PARAM] is a string constant or the name of a column containing UK postcodes.

[RESULT] = PSECT([PARAM])
Part Description
[RESULT] Data Type =
[PARAM] Data Type =

QUARTER

Returns the quarter of the year for each record (1-4).

[RESULT] = QUARTER([DATE_COLUM])
Part Description
[RESULT] Data Type =
[DATE_COLUMN] Data Type =

RAND

Returns a random number between 0 and the value of each record in the column.

[RESULT] = RAND([COLUM])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

REAL

Returns the integer or whole number part of the input data. Used in an expression to force the creation of a REAL_FIELD.

[RESULT] = REAL( [VALUE],[PRECISION])
Part Description
[RESULT] Data Type = REAL_FIELD
  • Returns NULL if [VALUE] cannot be converted to REAL format
[VALUE] Input parameter can be any of the following:
  • Numeric Literal
  • Column containing REAL or INTEGER Data
  • String column containing numbers
[PRECISION] Optional
  • Sets the precision of the output data
  • Must be between 0 and 6
  • If not specified, precision defaults to the precision set by CO_DefaultPrecision in the Cerberus.Ini file
Note:If [PRECISION] is specified, then the function must be called within the EXPR{} syntax. Otherwise it can be called directly.

RIGHT

Returns [number] characters from the right of the column.

[RESULT] = RIGHT([COLUMN],[NUMBER])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =
[NUMBER] Data Type =

RMEAN

Returns the average value of a set of values as a Real.

[RESULT] = RMean([FIELDNAME])
Part Description
[RESULT] Data Type = REAL_FIELD Average value of specified field as a Real.
[FIELDNAME] Data Type = INTEGER_FIELD, REAL_FIELD The field to obtain the average of.

ROW

As for IURN.

[RESULT] = ROW([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

RTRIM

Returns the values of a text column after removing any spaces to the right of the column.

[RESULT] = RTRIM
Part Description
[RESULT] Data Type = TEXT_FIELD, UNICODE_FIELD
[COLUMN] Data Type = TEXT_FIELD, UNICODE_FIELD

SECOND

Returns the seconds part of a datetime or time column.

[RESULT] = SECOND([DATETIME_COLUMN])
Part Description
[RESULT] Data Type = INTEGER_FIELD
[DATETIME_COLUMN] Data Type = DATETIME_FIELD, TIME_FIELD

SGN

Returns the sign of each record, -1 for negative numbers, +1 for positive numbers or 0. \

[RESULT] = SGN([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

SKEW

A measure of the skew of a column.

[RESULT] = SKEW([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

SORTFORMAT

Converts a postcode to Sort Format in a column containing UK postcodes.

[RESULT] = SORTFORMAT([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

SOUNDS

Returns the values of the column after removing all vowels (and Y) and spaces from the values.

[RESULT] = SOUNDS([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

SQRT

Returns the square root of each record.

[RESULT] = SQRT([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

STDEV

Returns the population standard deviation, that is the average amount by which a set of values deviates on either side of the mean for the column.

[RESULT] = StDev([FIELDNAME])
Part Description
[RESULT] Data Type = REAL_FIELD Standard deviation of specified field.
[FIELDNAME] Data Type = INTEGER_FIELD, REAL_FIELD The field from which to obtain the standard deviation.

STDEVS

Returns the sample standard deviation of a column.

[RESULT] = STDEVS([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

STRING

Returns a string representation of another data type. Used in an expression to force the creation of a TEXT_FIELD.

[RESULT] = String([VALUE])
Part Description
[RESULT] Data Type = TEXT_FIELD
[VALUE] Input parameter can be any valid data type or a fixed string.
  • Fixed String: String(12345) will return "12345"
  • Column: String([Demo].[Customer].[ID]) will convert the ID column to a text field.
Note:If the input is a Date, a Time or a DateTime the resulting string will be formatted in the user format, as specified in the Cerberus.ini file. STRING will not convert Unicode data to ASCII format.

STRIP

Returns the values of the column after removing all spaces.

[RESULT] = STRIP([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

SUM

Returns the sum of a columns data.

[RESULT] = Sum([FIELDNAME])
Part Description
[RESULT] Data Type = REAL_FIELD Sum of values.
[FIELDNAME] Data Type = INTEGER_FIELD or REAL_FIELD The field to sum.

SUMSQ

Returns the sum of squares of a set of values.

[RESULT] = SumSq([FIELDNAME])
Part Description
[RESULT] Data Type = REAL_FIELD Sum of squares of specified field.
[FIELDNAME] Data Type = INTEGER_FIELD, REAL_FIELD The field from which to obtain the sum of the squares.

THIRDQUARTILE

Returns the value at the third quartile of the column.

[RESULT] = THIRDQUARTILE([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

TIME

Returns a TIME_FIELD representation of the input data. Used in an expression to force the creation of a TIME_FIELD.

[RESULT] = TIME([VALUE],[FORMAT])
Part Description
[RESULT] Data Type = TIME_FIELD
  • Returns NULL if [VALUE] cannot be converted to TIME format
  • The Format of the RESULT is determined by the SystemTimeFormat
[VALUE] Input parameter can be any of the following:
  • Time Literal
  • Column containing formatted TIME data as string
  • Column containing DATETIME data
[FORMAT] Optional
  • String specifying the format of the Input data:
    • HH = Hours
    • MM = Minutes
    • SS = Seconds
  • If not specified, FORMAT defaults to the Time format specified by SystemTimeFormat in the Cerberus.ini file.
[VALUE] [FORMAT] [RESULT]
3348 (03:03:48) NONE 03:34:08
13458 (13:45:09) NONE 01:34:59
224618 (22:26:18) NONE 22:26:18
3348 (03:03:48) HHMMSS NULL
13458 (13:45:09) HHMMSS NULL
224618 (22:26:18) HHMMSS 22:26:18
184622 (22:26:18) SSMMHH 22:26:18

If [FORMAT] is not specified, and the input data is not fully qualified (instead of 01, a 1 is provided), the resulting time may not be correct.

If [FORMAT] is specified:

  • Engine will only evaluate the expression for data that is fully qualified to prevent incorrect formatting from being applied.
  • The function must be called within the EXPR{} syntax. Otherwise it can be called directly.
Note:When using a TIME_FIELD in an expression, note that hours are always stored as 24 hour.

TRIM

Returns the values of a text column after removing any spaces to the left and right of the column.

[RESULT] = TRIM([COLUMN])
Part Description
[RESULT] Data Type = TEXT_FIELD, UNICODE_FIELD
[COLUMN] Data Type = TEXT_FIELD, UNICODE_FIELD

TRIM is not an NSQL function, but can be used in NSQL with the Expr{} syntax:

SELECT EXPR{ TRIM([Demo].[Customer].[Town]) } FROM [Demo].[Customer];

UCASE

Returns the values of the column converted to upper case.

[RESULT] = UCASE([COLUMN])
Part Description
[RESULT] Data Type =
[COLUMN] Data Type =

WEEKDAY

Returns the day of the week for each record (1-7, with 1 being Monday).

[RESULT] = WEEKDAY([DATE_COLUMN])
Part Description
[RESULT] Data Type =
[DATE_COLUMN] Data Type =

WSTRING

Returns a string representation of another data type. Used in an expression to force the creation of a UNICODE_FIELD.

[RESULT] = String([VALUE])
Part Description
[RESULT] Data Type = UNICODE_FIELD
[VALUE] Input parameter can be any valid data type or a fixed string.
  • Fixed String: String(12345) will return "12345"
  • Column: String([Demo].[Customer].[ID]) will convert the ID column to a UNICODE text field.
Note:If the input is a Date, a Time or a DateTime the resulting string will be formatted in the user format, as specified in the Cerberus.ini file. WSTRING will convert ASCII input to Unicode format.

YEAR

Returns the year portion of a date.

[RESULT] = YEAR([DATE_COLUMN])
Part Description
[RESULT] Data Type =
[DATE_COLUMN] Data Type =
  Online & Instructor-Led Courses | Training Videos | Webinar Recordings
© Alterian. All Rights Reserved. | Privacy Policy | Legal Notice