Derivation formulas part 1: Operations and allowed values

Mathematical

<value> <operator> <value>

<value> can be:

• A number

• An entity component

• The following nested operations:

  • Mathematical

  • Numeric mapping

  • Average

  • Coalesce

<operator> can be:

• +

• -

• *

• /

• ^ (to the power of)

Examples:

Properties[Instrument/default/SharesOutstanding] * Properties[Instrument/default/Price]
(Properties[Instrument/default/Cost] + Properties[Instrument/default/Price]) / Properties[Instrument/default/SharesOutstanding]
(Properties[Instrument/default/Price] - Properties[Instrument/default/Cost]) ^ 2

Concatenation

concat(<value>, <value> [, value…])

<value> can be (there must be at least two, each separated by a comma):

• A number

• A string

• An entity component

• Any other nested operation, including another Concatenation

concat(Properties[Instrument/default/Name], ' - ', Properties[Instrument/default/Currency], '(', Properties[Instrument/default/Country], ')')
concat(Name, ' - ', Luid)

Numeric mapping

map(<value>: <expression>)

<value> can be:

• A number

• An entity component

• The following nested operations:

  • Mathematical

  • Numeric mapping

  • Average

  • Coalesce

<expression> is a comma-separated list of one or more of:

• <string> = <number>

• <number> = <number>

• default = <number>

map(Properties[Instrument/default/S_and_P]: 'AA'=1, 'BB'=2,'CC'=3, default=0)
map(Properties[Instrument/default/Duration]: '3 months'=3, '9 months'=9)

String mapping

map(<value>: <expression>)

<value> can be:

• A string

• An entity component

• The following nested operations:

  • Concatenation

  • String mapping

  • Coalesce

<expression> is a comma-separated list of one or more of:

• <string> = <string>

• <number> = <string>

• default = <string>

map(Properties[Instrument/default/Duration]: '3 months'='3m', '9 months'='9m')

map(Properties[Instrument/default/Duration]: '3 months'='3m', '9 months'='9m', Default = '0m')

Average,
Minimum, Maximum,
Sum

average(<value> [, value…])

min(<value> [, value…])

max(<value> [, value…])

sum(<value> [, value…])

<value> can be (there can be multiple separate values, or just one if it is a data field that is a list):

• A number

• An entity component

• The following nested operations:

  • Mathematical

  • Numeric mapping

  • Coalesce

  • Abs

  • Another Average, Minimum, Maximum or Sum operation 

Note these operations return a single number. 

average(map(Properties[Instrument/default/S_and_P]: 'AA'=1, 'BB'=2,'CC'=3), map(Properties[Instrument/default/Moodys]: 'A'=1, 'B+'=2, 'C'=3), min(map(Properties[Instrument/default/S_and_P]: 'AA'=1, 'BB'=2,'CC'=3), map(Properties[Instrument/default/Moodys]: 'A'=1, 'B+'=2, 'C'=3) max(map(Properties[Instrument/default/S_and_P]: 'AA'=1, 'BB'=2,'CC'=3), map(Properties[Instrument/default/Moodys]: 'A'=1, 'B+'=2, 'C'=3) sum(abs(1, -2, 3))

Absolute

abs(<value> [, value…])

<value> can be (there can be multiple separate values, or just one if it is a data field that is a list):

• A number

• An entity component

• The following nested operations:

  • Mathematical

  • Numeric mapping

  • Average, Minimum, Maximum or Sum

Note this operation returns a single number if just one is passed in. Otherwise, it returns a list of numbers. 

abs(-5)
abs(1, -2, 3)

Coalesce

coalesce(<value>, <value>…)

<value> can be (there must be at least two, each separated by a comma):

• A number

• A string

• An entity component

• Any other nested operation, including another Coalesce

Note 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. 

coalesce(Properties[Transaction/Commbank/description], Properties[Transaction/Monzo/Description], Properties[Transaction/AmericanExpress/Description], 'Unknown') coalesce(Properties[Transaction/Commbank/Price], Properties[Transaction/Monzo/Price], Properties[Transaction/AmericanExpress/Price], -1)

Replace

replace(<value>: <string> = <string>)

<value> can be:

• A string

• An entity component

• The following nested operations:

  • Concatenation

  • String mapping

  • Coalesce

Replace(Name: 'instr'='instrument')

If

if(<value> <operator> <value>) 
then <trueValue> 
else <falseValue>

<value> can be:

• A number

• A string

• An entity component

• Any other 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 and False respectively.

• A number

• A string

• An entity component

• Any other nested operation, including another If

if(Properties[Transaction/Commbank/Price] gt Properties[Transaction/Commbank/Cost]) then True else False
if(Name eq 'InstrumentCommbank' and State eq 'Active') then 'Commbank' else Name

if(Properties[Instrument/Ibor/Country] neq 'UK' or Properties[Instrument/Ibor/Region] neq 'EMEA') then 'Others' else Properties[Instrument/Ibor/Country]

Convert to string

toString(<value>)

<value> can be:

• A number, with other data types untested but likely to work

• An entity component

• Any other nested operation, including another Convert to string

Convert to number

toNumber(<value>)

<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 other nested operation providing it returns a number

If <value> cannot be parsed (for example toNumber("hello")) the operation returns 0.