Skip to main content

Celonis Product Documentation

QUANTITY_CONVERT
Description

This operator converts quantity units based on a QUANTITY_CONVERSION_RATES table, which is optionally provided by the user. If not provided by the user, a default QUANTITY_CONVERSION_RATES table will be used.

The conversion rate is taken from the QUANTITY_CONVERSION_RATES table based on the original unit of the item, the ID of the item, and the requested unit to convert to. The output of the operator is a new column containing the values of the converted unit amounts.

The QUANTITY_CONVERSION_RATES table should have the following columns:

  • ConversionQualifier: The ConversionQualifier which identifies each conversion rate, must be of type STRING.

  • FromUnit: From unit of the conversion rate, must be of type STRING.

  • ToUnit: To unit of the conversion rate, must be of type STRING.

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

These column names are case insensitive. Furthermore, the conversion rate is taken to be valid for only rows which are unique in the combination [ConversionQualifier, FromUnit, ToUnit] if they are trying to be matched.

Syntax
 QUANTITY_CONVERT ( amount, FROM(from_unit), TO(to_unit) [, identifier, [ quantity_conversion_rates ] ] )

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

  • from_unit: The unit of the original amount, must be a constant or a column of type STRING.

  • to_unit: The requested unit to convert to, must be a constant or a column of type STRING.

  • identifier: The identifier of the original quantity, must be a constant or a column of type STRING.

  • quantity_conversion_rates: The table containing the conversion rates. It must contain the columns as described above. If not specified, "QUANTITY_CONVERSION_RATES" is taken as the default name for the conversion rates table. This table should be present in the datamodel, but it is not required to be joined to any other table.

Note: The three given expressions amount, from_unit, to_unit and, if provided, identifier must have a common table.

Behavior details

A NULL-value is returned if no conversion rate can be found in the QUANTITY_CONVERSION_RATES table for the given to_unit, from_unit and ident parameters.

NULL handling

If an entry in the amount, to_unit or from_unit column is NULL, the corresponding result in that row will also be NULL.

Examples

[1]

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

Query

Column1 

         QUANTITY_CONVERT ( "INPUT"."AMOUNT" , FROM ( "INPUT"."UNIT" ) , TO ( 'ITEMS' ) , "INPUT"."ID" )

Input

Output

INPUT

ID : string

UNIT : string

AMOUNT : float

'3'

'SMALL BOX'

1.5

'1'

'BIG BOX'

2.5

'3'

'SMALL BOX'

3.0

QUANTITY_CONVERSION_RATES

ConversionQualifier : string

FromUnit : string

ToUnit : string

Rate : float

'1'

'BIG BOX'

'ITEMS'

100.0

'2'

'SMALL BOX'

'ITEMS'

14.0

'3'

'SMALL BOX'

'ITEMS'

12.0

Result

Column1

18.0

250.0

36.0

[2]

An example of converting two different units in 'ITEMS'-units with a given quantity-conversion-rates table 'QCR':

Query

Column1 

         QUANTITY_CONVERT ( "INPUT"."AMOUNT" , FROM ( "INPUT"."UNIT" ) , TO ( 'ITEMS' ) , "INPUT"."ID" , "QCR" )

Input

Output

INPUT

ID : string

UNIT : string

AMOUNT : float

'3'

'SMALL BOX'

1.5

'1'

'BIG BOX'

2.5

'3'

'SMALL BOX'

3.0

QCR

ConversionQualifier : string

FromUnit : string

ToUnit : string

Rate : float

'1'

'BIG BOX'

'ITEMS'

100.0

'3'

'SMALL BOX'

'PAIRS'

6.0

'3'

'SMALL BOX'

'ITEMS'

12.0

Result

Column1 : float

18.0

250.0

36.0

[3]

An example in which the ID of the input quantity is not listed in the QUANTITY_CONVERSION_RATES table. In this case, for that ID a NULL-value is returned:

Query

Column1 

         QUANTITY_CONVERT ( "INPUT"."AMOUNT" , FROM ( "INPUT"."UNIT" ) , TO ( 'ITEMS' ) , "INPUT"."ID" )

Input

Output

INPUT

ID : string

UNIT : string

AMOUNT : float

'3'

'SMALL BOX'

1.5

'1'

'BIG BOX'

2.5

'4'

'SMALL BOX'

3.0

QUANTITY_CONVERSION_RATES

ConversionQualifier : string

FromUnit : string

ToUnit : string

Rate : float

'1'

'BIG BOX'

'ITEMS'

100.0

'2'

'SMALL BOX'

'ITEMS'

14.0

'3'

'SMALL BOX'

'ITEMS'

12.0

Result

Column1 : float

18.0

250.0

null

Warning

QUANTITY_CONVERT: ConversionQualifier, ToUnit or FromUnit is either a NULL-value or not found in the conversion rates table [QUANTITY_CONVERSION_RATES]. Affected value: [[index: 2]].

[4]

An example demonstrating the behaviour of QUANTITY_CONVERT when Amount, fromUnit and ToUnit are constant and only the IDs are not:

Query

Column1 

         QUANTITY_CONVERT ( 1.0 , FROM ( 'BIG BOX' ) , TO ( 'ITEMS' ) , "INPUT"."ID" )

Input

Output

INPUT

ID : string

'1'

'1'

'1'

QUANTITY_CONVERSION_RATES

ConversionQualifier : string

FromUnit : string

ToUnit : string

Rate : float

'1'

'BIG BOX'

'ITEMS'

100.0

'1'

'BIG BOX'

'PAIRS'

100.0

'1'

'SMALL BOX'

'PAIRS'

6.0

'1'

'SMALL BOX'

'ITEMS'

12.0

Result

Column1 : float

100.0

100.0

100.0