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

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

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

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

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

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

Syntax
  CURRENCY_CONVERT ( to_currency, from_currency, amount, date, currency_conversion_rates )
 
  • to_currency: The requested currency to convert to, must be a constant of type STRING.

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

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

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

  CURRENCY_CONVERT ( to_currency, from_currency, amount[, date ])
 

A short version of the syntax as given above:

  • date: if omitted, TODAY() is taken as the default date.

  • currency_conversion_rates: "CURRENCY_CONVERSION_RATES" is taken as the default name for the conversion rates table. This table should be present in the datamodel.

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.

  • An error is returned if no entry can be found for the requested to and from currencies.

NULL handling

If either the from currency, amount, or date 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 ( 'INR' , "INPUT"."CURRENCY" , "INPUT"."AMOUNT" , "INPUT"."DATE" , "CURRENCY_CONVERSION_RATES" )
        

Input

Output

CURRENCY_CONVERSION_RATES

FROM_CURRENCY : string

TO_CURRENCY : string

RATE : float

FROM_DATE : date

TO_DATE : 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 ( 'INR' , "INPUT"."CURRENCY" , "INPUT"."AMOUNT" , "INPUT"."DATE" )
        

Input

Output

CURRENCY_CONVERSION_RATES

FROM_CURRENCY : string

TO_CURRENCY : string

RATE : float

FROM_DATE : date

TO_DATE : 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 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 ( 'INR' , "INPUT"."CURRENCY" , "INPUT"."AMOUNT" , "INPUT"."DATE" , "CURRENCY_CONVERSION_RATES" )
        

Input

Output

CURRENCY_CONVERSION_RATES

FROM_CURRENCY : string

TO_CURRENCY : string

RATE : float

FROM_DATE : date

TO_DATE : 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]].

See also: