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.
Note: A derivation formula is stored as part of a derived property type.
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.
- The term ‘entity object’ in the table below is used to refer to all the fields, properties, identifiers and other data structures belonging to entities that are available to be operated on in the formula.
Operation | Syntax | Allowed values |
Mathematical | <value> <operator> <value> |
|
Examples:
| ||
Concatenation | concat(<value>, <value>…) |
|
concat(Properties[Instrument/default/Name], ' - ', Properties[Instrument/default/Currency], '(', Properties[Instrument/default/Country], ')') concat(Name, ' - ', Luid) | ||
Numeric mapping | map(<value>: <expression>) |
|
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>) |
|
map(Properties[Instrument/default/Duration]: '3 months'='3m', '9 months'='9m') | ||
Average | average(<value>, <value>…) |
|
average(Properties[Instrument/default/Price], Properties[Instrument/default/Cost]) | ||
Coalesce | coalesce(<value>, <value>…) |
|
Note Coalesce prefers property values in the order they are specified. So in the first example below, the derived property takes its description value from
| ||
Replace | replace(<value>: <string> = <string>) |
|
Replace(Name: 'instr'='instrument') | ||
If | if(<value> <operator> <value>) then <trueValue> else <falseValue> |
|
if(Properties[Transaction/Commbank/Price] gt Properties[Transaction/Commbank/Cost]) then 'true' else 'false' | ||
Convert to string | toString(<value>) |
|
Convert to number | toNumber(<value>) |
If |
Using entity objects in derivation formulae
The data structures belonging to entities that can be used in a derivation formula differ depending on the type of entity the derived property belongs to.
For example, if the derived property belongs to holdings (that is, the first part of the 3-stage property key signals it is in the Holding
domain), then values can derive from:
- Any other property in the
Holding
domain, and/or - Certain fields on holdings themselves, and/or
- Certain fields, identifiers, sub-holding keys and properties on other entities.
Consider the following example, of a derivation formula for a Holding
derived property that consists of a series of If operations and a nested Concat operation, all conditional on the HoldingType
entity field:
else if ( HoldingType eq 'R' ) then 'Payable/Receivable'
else if ( HoldingType eq 'B' ) then 'Cash'
else concat('Unknown holding type ', HoldingType)
Entity (derived property domain) | ||||
Instrument | Portfolio | Transaction | Holding | |
Fields on this type of entity that can be used in a formula | LusidInstrumentId Name State LookthroughPortfolio.Scope LookthroughPortfolio.Code | Id.Scope Id.Code Type DisplayName Description Created IsDerived BaseCurrency ParentPortfolioId.Scope ParentPortfolioId.Code | TransactionId CounterpartyId TransactionDate SettlementDate EntryDateTime Units ExchangeRate Source TransactionCurrency InstrumentUid Type TransactionPrice.Price TransactionPrice.Type TotalConsideration.Amount TotalConsideration.Currency |
|
Identifiers on this type of entity | For example:Identifiers[ClientInternal] Identifiers[Figi] Identifiers[LusidInstrumentId] | n/a | For example:InstrumentIdentifiers[Instrument/default/ClientInternal] InstrumentIdentifiers[Instrument/default/Figi] InstrumentIdentifiers[Instrument/default/LusidInstrumentId] | n/a |
Properties on this type of entity | Custom properties, for example: System properties, for example: | Custom properties, for example: System properties, for example: | Custom properties, for example: System properties, for example: | Custom properties, for example: System properties, for example: |
Objects on other entity types | n/a | n/a | Instrument properties (note you must also decorate the instrument property when decorating the derived property onto retrieved transactions). For example: | Transaction fields: |