Skip to main content

Celonis Product Documentation

CURRENCY_CONVERT
Description

This operator converts currencies based on a provided CURRENCY_CONVERSION_RATES table.

For the SAP specific version of this operator see CURRENCY_CONVERT_SAP.

The conversion rate is taken from the CURRENCY_CONVERSION_RATES table based on the original currency of the transaction, the date of the transaction, and the requested currency to convert to. If specified, also an exchange rate type and a source system instance are taken into account. The output of the operator is a new column containing the values of the converted transaction amounts.

The CURRENCY_CONVERSION_RATES table should have the following columns:

  • FromCurrency: From currency of the conversion rate, must be of type STRING.

  • ToCurrency: To currency of the conversion rate, must be of type STRING.

  • Rate: The conversion rate, must be of type FLOAT.

  • FromDate: The start date from which the conversion rate is valid, must be of type DATE.

  • ToDate: The end date until which the conversion rate is valid, must be of type DATE.

  • ExchangeRateType: The exchange rate type, must be of type STRING. This column is only required if the exchange_rate_type parameter is specified.

  • SourceSystemInstance: The source system identifier, must be of type STRING. This column is only required if the source_system_instance parameter is specified.

Syntax
  CURRENCY_CONVERT ( amount, FROM ( from_currency ), TO ( to_currency ), date [, currency_conversion_rates [, exchange_rate_type [, <source_system_instance ] ] ] )

  • amount: The original amount we want to convert, must be a column of type FLOAT.

  • from_currency: The currency of the original amount, must be a column of type STRING.

  • to_currency: The requested currency to convert to, must be a constant of type STRING.

  • date: The date of the original transaction, must be a column of type DATE.

  • currency_conversion_rates: The CURRENCY_CONVERSION_RATES table, must contain the columns as described above.

  • exchange_rate_type: The exchange rate type for which the currency is to be converted, must be a column of type STRING. This column is optional, but must be specified if the conversion rates table contains an ExchangeRateType column.

  • source_system_instance: The source system identifier for which the currency is to be converted, must be a column of type STRING. This column is optional, but must be specified if the conversion rates table contains a SourceSystemInstance column.

Behavior details

If the requested conversion rate cannot be found in the CURRENCY_CONVERSION_RATES table, one of three things can happen:

  • If the CURRENCY_CONVERSION_RATES table contains entries for the requested to and from currencies at other date intervals:

    • The most recent conversion rate up to the requested date is used and a warning is returned.

    • If no previous rate exists, the earliest conversion rate after the requested date is used and a warning is returned.

  • If the to and from currencies are the same, the original amount is returned.

  • NULL values are returned if no entry can be found for the requested to and/or from currencies and the to and from currencies are not equal.

NULL handling

If either the from currency, amount, or date (or if specified, the exchange rate type or source system instance) of the original transaction is NULL, the resulting value is NULL as well. If an entry in the CURRENCY_CONVERSION_RATES table contains a NULL value in any of its columns, this entry is ignored.

Examples

[1]

An example of converting two transactions in 'USD' to 'INR':

Query

Column1 

         CURRENCY_CONVERT ( "INPUT"."Amount" , FROM ( "INPUT"."Currency" ) , TO ( 'INR' ) , "INPUT"."Date" , "CURRENCY_CONVERSION_RATES" )

Input

Output

CURRENCY_CONVERSION_RATES

FromCurrency : string

ToCurrency : string

Rate : float

FromDate : date

ToDate : date

'USD'

'INR'

74.4

Fri Jan 01 2021 00:00:00.000

Sat Jan 30 2021 00:00:00.000

'USD'

'INR'

74.6

Mon Feb 01 2021 00:00:00.000

Sun Feb 28 2021 00:00:00.000

INPUT

Currency : string

Amount : float

Date : date

'USD'

1.0

Fri Jan 15 2021 00:00:00.000

'USD'

1.0

Mon Feb 15 2021 00:00:00.000

Result

Column1 : float

74.4

74.6

[2]

An example of using the short syntax of the operator. The default name for the CURRENCY_CONVERSION_RATES table is used:

Query

Column1 

         CURRENCY_CONVERT ( "INPUT"."Amount" , FROM ( "INPUT"."Currency" ) , TO ( 'INR' ) , "INPUT"."Date" )

Input

Output

CURRENCY_CONVERSION_RATES

FromCurrency : string

ToCurrency : string

Rate : float

FromDate : date

ToDate : date

'USD'

'INR'

74.4

Fri Jan 01 2021 00:00:00.000

Sat Jan 30 2021 00:00:00.000

'USD'

'INR'

74.6

Mon Feb 01 2021 00:00:00.000

Sun Feb 28 2021 00:00:00.000

INPUT

Currency : string

Amount : float

Date : date

'USD'

1.0

Fri Jan 15 2021 00:00:00.000

'USD'

1.0

Mon Feb 15 2021 00:00:00.000

Result

Column1 : float

74.4

74.6

[3]

An example of converting several transactions in 'USD' to 'INR' specifying an exchange_rate_type and a source_system_instance:

Query

Column1 

         CURRENCY_CONVERT ( "INPUT"."Amount" , FROM ( "INPUT"."Currency" ) , TO ( 'INR' ) , "INPUT"."Date" , "CURRENCY_CONVERSION_RATES" , "INPUT"."ExchangeRateType" , "INPUT"."SourceSystemInstance" )

Input

Output

CURRENCY_CONVERSION_RATES

FromCurrency : string

ToCurrency : string

Rate : float

FromDate : date

ToDate : date

ExchangeRateType : string

SourceSystemInstance : string

'USD'

'INR'

74.4

Fri Jan 01 2021 00:00:00.000

Sat Jan 30 2021 00:00:00.000

'BUY'

'SAP'

'USD'

'INR'

70.4

Fri Jan 01 2021 00:00:00.000

Sat Jan 30 2021 00:00:00.000

'SELL'

'SAP'

'USD'

'INR'

75.2

Fri Jan 01 2021 00:00:00.000

Sat Jan 30 2021 00:00:00.000

'BUY'

'ORACLE'

'USD'

'INR'

75.1

Fri Jan 01 2021 00:00:00.000

Sat Jan 30 2021 00:00:00.000

'SELL'

null

'USD'

'INR'

74.6

Mon Feb 01 2021 00:00:00.000

Sun Feb 28 2021 00:00:00.000

'BUY'

'SAP'

'USD'

'INR'

70.6

Mon Feb 01 2021 00:00:00.000

Sun Feb 28 2021 00:00:00.000

'SELL'

'SAP'

INPUT

Currency : string

Amount : float

Date : date

ExchangeRateType : string

SourceSystemInstance : string

'USD'

1.0

Fri Jan 15 2021 00:00:00.000

'BUY'

'SAP'

'USD'

1.0

Fri Jan 15 2021 00:00:00.000

'BUY'

'ORACLE'

'USD'

1.0

Mon Feb 15 2021 00:00:00.000

'BUY'

'SAP'

Result

Column1 : float

74.4

75.2

74.6

[4]

An example in which the date of the original transaction is not contained in any of the intervals in the CURRENCY_CONVERSION_RATES table. In this case, the latest known conversion rate up to the requested date is used and a warning is returned:

Query

Column1 

         CURRENCY_CONVERT ( "INPUT"."Amount" , FROM ( "INPUT"."Currency" ) , TO ( 'INR' ) , "INPUT"."Date" , "CURRENCY_CONVERSION_RATES" )

Input

Output

CURRENCY_CONVERSION_RATES

FromCurrency : string

ToCurrency : string

Rate : float

FromDate : date

ToDate : date

'USD'

'INR'

74.6

Mon Feb 01 2021 00:00:00.000

Sun Feb 28 2021 00:00:00.000

'USD'

'INR'

75.0

Thu Apr 01 2021 00:00:00.000

Sat May 01 2021 00:00:00.000

INPUT

Currency : string

Amount : float

Date : date

'USD'

1.0

Sat May 15 2021 00:00:00.000

Result

Column1 : float

75.0

Warning

CURRENCY_CONVERT: The input date column contains a date for which no conversion rate could be found in the conversion rates table [CURRENCY_CONVERSION_RATES]. In this case, the latest conversion rate up to that date is used. Affected case: [[date: 2021-05-15T00:00:00, currency: USD]].

[5]

An example demonstrating the behavior of CURRENCY_CONVERT when ToCurrency and FromCurrency are equal.

Query

Column1 

         CURRENCY_CONVERT ( 2.3 , FROM ( 'USD' ) , TO ( 'USD' ) , {d '2021-01-24' } , "CURRENCY_CONVERSION_RATES" )

Input

Output

(empty table)

Result

Column1 : float

2.3

See also: