A derived property must have a derivation formula that instructs LUSID how to automatically calculate values from one or more data fields or properties, including other derived properties.
Required additional reading: Derivation formulas part 2: Entity components
A derivation formula consists of one or more operations on one or more values. Note the following:
Operations can be nested. So for example a Mathematical operation can add a number to the result of another, nested Mathematical operation.
Operations can be chained within the If operation using the
AND
/OR
logical operators.The term entity component in the table below is used to refer to all the data fields, properties, identifiers and other data structures belonging to entities that are available to be operated on in the formula.
Mathematical operations
Syntax
Allowed values
<operator>
can be +
, -
, *
, /
or ^
(to the power of).
<value>
can be:
A number
The following nested operations:
Another Mathematical operation
Note the following:
A null value (that is, a missing value) is treated as zero, so
5 * null = 0
.Dividing by zero results in a zero value, so
5 / 0 = 0
and5 / null = 0
.
Examples
Concatenation
Syntax
Allowed values
<value>
can be (there must be at least two, each separated by a comma):
A number
A string
Any nested operation, including another Concatenation
Note this operation concatenates null values to null, so Concat(null, null) = null
.
Examples
Numeric mapping
Syntax
Allowed values
<value>
can be:
A number
The following nested operations:
Another Numeric mapping operation
<expression>
is a comma-separated list of one or more of:
<string>=<number>
<number>=<number>
default=<number>
Note this operation maps a null value to null if no default is set, so:
Map(null: 'AA'=1) = null
Map(null: 'AA'=1, default=0) = 0
Examples
String mapping
Syntax
Allowed values
<value>
can be:
A string
The following nested operations:
Another String mapping operation
<expression>
is a comma-separated list of one or more of:
<string>=<string>
<number>=<string>
default=<string>
Note this operation maps a null value to null if no default is set, so:
Map(null: '3 months'='3m') = null
Map(null: '3 months'='3m', default='0m') = 0m
Examples
Average, Minimum, Maximum, Sum
Syntax
Allowed values
<value>
can be (there can be multiple separate values, or just one if it is a data field that is a list):
A number
The following nested operations:
Another Average, Minimum, Maximum or Sum operation
Note these operations treat a null value as 0, so Average(1, null, 5) = 2
.
Examples
These operations return a single number.
Count
Note: This operation can only be used in compliance rules.
Syntax
Allowed values
<value>
must be a list. The list may be:
An entity component that is a list
A nested operation that results in a list
Note this operation counts a null value as null, so Count(null) = null
.
Examples
Absolute
Syntax
Allowed values
<value>
can be (there can be multiple separate values, or just one if it is a data field that is a list):
A number
The following nested operations:
Note this operation treats a null value as 0, so Abs(null) = 0
.
Examples
This operation returns a single number if just one is passed in. Otherwise, it returns a list of numbers.
Round, RoundUp, RoundDown
Syntax
The Round
operation combines the functionality of the Excel Round and MRound functions. The <multiple>
is optional.
If you provide a <multiple>
for Round
, the operation behaves like Excel MRound
. If you do not, it behaves like Excel Round(<value>, 0)
.
To emulate Excel
Round
with positive<num_digits>
to round to a number of decimal places, specify a<multiple>
that is a fraction of 1, so for exampleRound(567.1234, 0.01) = 567.12
.To emulate Excel
Round
with negative<num_digits>
to round to the left of the decimal point, specify a<multiple>
that is a multiple of 1, so for exampleRound(567.1234, 100) = 600
.
Note that specifying Round
with a positive <value>
and a negative <multiple>
behaves like Excel MRound
and returns null, so Round(567.1234, -2) = null
.
The behavior of the RoundUp
and RoundDown
operations is similar:
If you provide a
<multiple>
forRoundUp
, the operation behaves like Excel Ceiling. If you do not, it behaves like ExcelRoundUp(<value>, 0)
.If you provide a
<multiple>
forRoundDown
, the operation behaves like Excel Floor. If you do not, it behaves like ExcelRoundDown(<value>, 0)
.
Allowed values
<value>
can be:
A number
The following nested operations:
<multiple>
is an optional number to round to, with the caveat that it must have the same sign as <value>
.
Note these operations treat a null value as 0, so Round(null) = 0
.
Examples
Round | RoundUp | RoundDown |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Coalesce
Syntax
Allowed values
<value>
can be (there must be at least two, each separated by a comma):
A number
A string
Any nested operation, including another Coalesce
Note this operation coalesces null values to null, so Coalesce(null, null) = null
.
Examples
This operation gives preference to property values in the order they are specified. So in the first example below, the derived property takes its description value from Commbank
if that property exists, from Monzo
if not, and finally from AmericanExpress
if neither. If none of these properties exist, the derived property has the value Unknown
.
Replace
Syntax
Allowed values
<value>
can be:
A string
The following nested operations:
Note this operation replaces a null value with null, so Replace(null: 'instr'='instrument') = null
.
Examples
Lower, Upper, Proper, Sentence
Syntax
Allowed values
<value>
can be:
A string
The following nested operations:
Another Lower, Upper, Proper or Sentence operation
Examples
Substring
Syntax
Returns a substring of <value>
, from <startIndex>
(where 1 is the first character) for <length>
characters, or until the end if no <length>
is specified.
Allowed values
<value>
can be:
A string
The following nested operations:
<startIndex>
and <length>
must be positive integers. This operation returns an empty string if <startIndex>
is greater than the length of <value>
.
Examples
Convert to string
Syntax
Allowed values
<value>
can be:
A number, with other data types untested but likely to work
Any nested operation, including another Convert to string
Note this operation converts a null value to an empty string.
Convert to number
Syntax
Allowed values
<value>
can be:
A string representing a number, for example
"1"
or"1.123"
(converting other data types to numbers should be supported soon)An entity component that returns a number
Any nested operation providing it returns a number
Note this operation converts the following to 0:
A value that cannot be parsed, so
Tonumber("hello") = 0
A null value, so
Tonumber(null) = 0
If then else
Syntax
Allowed values
<value>
can be:
A number
A string
Any nested operation, including another If
<operator>
can be any of these filter operators, for example:
eq
neq
gt
startswith
…
<trueValue>
and <falseValue>
can be:
The reserved words
True
andFalse
respectively.A number
A string
Any nested operation, including another If
Examples