Skip to main content

Celonis Product Documentation

INTERPOLATE
Description

INTERPOLATE can be used to interpolate missing values (i.e. NULL values). It is possible to specify a column based ordering and partitioning. The output type is INT if input column is INT and <interpolation type> is CONSTANT. FLOAT otherwise.

Syntax
  INTERPOLATE ( column, interpolation type [, ORDER BY ( sort_column [sorting], ... )] [, PARTITION BY ( partition_column, ... )] )

  • column: The input column where values should be interpolated. Supported input types: INT, FLOAT

  • interpolation type: Specify how the missing values should be interpolated:

    • CONSTANT: Use same values as previous data point

    • LINEAR: Use slope between enclosing data points

  • sort_column: Optional sorting column to specify an order.

  • sorting: Each of these columns can have an optional tag specifying the ordering of the column. Default is ascending:

    • ASC: Ascending order

    • DESC: Descending order

  • partition_column: Optional partition column to specify groups in which INTERPOLATE should operate.

One or more columns can be given to specify an ordering. This tells the INTERPOLATE function what the preceding/following element actually is. Optionally every column can be tagged as ascending or descending. The partition columns specify groups. The INTERPOLATE function operates independently within every group. This means when an ordering is given it is applied within every group.

Examples
CONSTANT

[1]

Simple example of CONSTANT interpolation of INT values.

Query

Column1 

         INTERPOLATE ( "Table1"."column" , CONSTANT )

Input

Output

Table1

column : int

1

null

3

4

Result

Column1 : int

1

1

3

4

[2]

Leading NULL values are filled with the first value found.

Query

Column1 

         INTERPOLATE ( "Table1"."column" , CONSTANT )

Input

Output

Table1

column : float

null

null

1.0

null

null

Result

Column1 : float

1.0

1.0

1.0

1.0

1.0

[3]

If no data point is found, NULL values cannot be interpolated.

Query

Column1 

         INTERPOLATE ( "Table1"."column" , CONSTANT )

Input

Output

Table1

column : int

null

null

null

Result

Column1

null

null

null

LINEAR

[4]

Simple example of LINEAR interpolation.

Query

Column1 

         INTERPOLATE ( "Table1"."column" , LINEAR )

Input

Output

Table1

column : int

1

null

3

4

Result

Column1 : float

1.0

2.0

3.0

4.0

[5]

For leading and lagging NULL values slope 0 is used (same as CONSTANT interpolation).

Query

Column1 

         INTERPOLATE ( "Table1"."column" , LINEAR )

Input

Output

Table1

column : int

null

2

3

4

null

Result

Column1 : float

2.0

2.0

3.0

4.0

4.0

[6]

If no data point is found, NULL values cannot be interpolated.

Query

Column1 

         INTERPOLATE ( "Table1"."column" , LINEAR )

Input

Output

Table1

column : int

null

null

null

Result

Column1

null

null

null

Ordering, Partitioning

[7]

CONSTANT interpolation with order and partition columns.

Query

Column1 

         INTERPOLATE ( "Table1"."activity" , CONSTANT , ORDER BY ( "Table1"."timestamp" ) , PARTITION BY ( "Table1"."case" ) )

Input

Output

Table1

case : int

activity : int

timestamp : date

1

null

Mon Feb 01 2016 02:00:00.000

1

null

Mon Feb 01 2016 01:00:00.000

2

null

Mon Feb 01 2016 03:00:00.000

1

300

Mon Feb 01 2016 03:00:00.000

2

null

Mon Feb 01 2016 04:00:00.000

2

100

Mon Feb 01 2016 01:00:00.000

2

200

Mon Feb 01 2016 02:00:00.000

Result

Column1 : int

300

300

200

300

200

100

200

[8]

LINEAR interpolation with order and partition columns.

Query

Column1 

         INTERPOLATE ( "Table1"."activity" , LINEAR , ORDER BY ( "Table1"."timestamp" ) , PARTITION BY ( "Table1"."case" ) )

Input

Output

Table1

case : int

activity : int

timestamp : date

5

20

Mon Feb 01 2016 02:00:00.000

1

null

Mon Feb 01 2016 02:00:00.000

1

500

Mon Feb 01 2016 01:00:00.000

2

null

Mon Feb 01 2016 03:00:00.000

1

700

Mon Feb 01 2016 03:00:00.000

2

null

Mon Feb 01 2016 04:00:00.000

2

null

Mon Feb 01 2016 01:00:00.000

2

200

Mon Feb 01 2016 02:00:00.000

3

null

Mon Feb 01 2016 01:00:00.000

3

null

Mon Feb 01 2016 02:00:00.000

3

null

Mon Feb 01 2016 03:00:00.000

4

null

Mon Feb 01 2016 01:00:00.000

4

1200

Mon Feb 01 2016 02:00:00.000

4

1600

Mon Feb 01 2016 04:00:00.000

4

null

Mon Feb 01 2016 03:00:00.000

4

null

Mon Feb 01 2016 05:00:00.000

5

10

Mon Feb 01 2016 01:00:00.000

5

30

Mon Feb 01 2016 03:00:00.000

Result

Column1 : float

20.0

600.0

500.0

200.0

700.0

200.0

200.0

200.0

null

null

null

1200.0

1200.0

1600.0

1400.0

1600.0

10.0

30.0

[9]

It is possible to combine an arbitrary amount of order columns with an arbitrary amount of partition columns.

Query

Column1 

         INTERPOLATE ( "Table1"."value" , LINEAR , ORDER BY ( "Table1"."year" ASC , "Table1"."column" DESC ) , PARTITION BY ( "Table1"."Country" , "Table1"."State" ) )

Input

Output

Table1

column : int

year : date

value : float

Country : string

State : string

4

Tue Dec 31 2002 00:00:00.000

null

'Germany'

'Bavaria'

2

Fri Dec 31 1999 00:00:00.000

2.0

'Germany'

'Berlin'

2

Mon Dec 31 2001 00:00:00.000

3.0

'Germany'

'Bavaria'

1

Fri Dec 31 1999 00:00:00.000

null

'Germany'

'Bavaria'

1

Fri Dec 31 1999 00:00:00.000

5.0

'USA'

'California'

1

Sun Dec 31 2000 00:00:00.000

6.0

'Germany'

'Berlin'

5

Tue Dec 31 2002 00:00:00.000

null

'USA'

'California'

6

Mon Dec 31 2001 00:00:00.000

null

'Germany'

'Berlin'

1

Tue Dec 31 2002 00:00:00.000

9.0

'Germany'

'Bavaria'

7

Wed Dec 31 2003 00:00:00.000

10.0

'USA'

'California'

Result

Column1 : float

6.0

2.0

3.0

3.0

5.0

6.0

7.5

6.0

9.0

10.0