Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

FACTORY_CALENDAR

Applies to: CELONIS 4.7

Description

FACTORY_CALENDAR produces a new factory calendar with the configuration as passed to it.

The factory calendar allows to restrict the accounted time to fine-grained intervals. These date intervals (e.g., [2020-01-30 09:21:30.500..2020-01-30 15:00:00.000]) specifies on which exact time work was done. The intervals are defined by the given start and end column in the FACTORY_CALENDAR input configuration. The factory calendar itself is a table which has to be added to the data model. This table needs to have at least a start date column and an end date column. These can then be used by the FACTORY_CALENDAR function. For example, a factory calendar table could contain uptime intervals of a machine. Applying this to the REMAP_TIMESTAMPS function for example, allows to calculate the exact throughput for this machine's uptime. Please note that the date intervals in the factory calendar table will be pre-processed when used to create a factory calendar. For example, overlapping date intervals will be joined together and counted only once in the date time calculations.

The factory calendar is used as input for functions like REMAP_TIMESTAMPS. In these contexts the calendar allows to restrict the accounted DATEs which are used for the date based calculations. By this, only valid time intervals as configured in the factory calendar will be considered.

Syntax

We can create a new factory calendar by using either of the two syntaxes shown below. The first syntax creates a single factory calendar from a start date and end date column and an optional filter condition, whereas the second syntax creates multiple factory calendars by making use of a table that has at least 3 columns named IDENT, START, and END. More details about the table and the required columns can be found below.

FACTORY_CALENDAR (  calendar_table.start_date_column, calendar_table.end_date_column [, filter_condition ] )
  • start_date_column: The date column containing the start dates of the date intervals.

  • end_date_column: The date column containing the end dates of the date intervals.

  • filter_condition: A filter condition to be applied.

FACTORY_CALENDAR ( calendar_table [, filter_condition ] )
  • calendar_table: Table with three columns.

    • IDENT: STRING column specifying calendar IDs.

    • START: The date column containing the start dates of the date intervals.

    • END: The date column containing the end dates of the date intervals.

  • filter_condition: A filter condition to be applied.

NULL handling

If one of the two columns (start_date_column or end_date_column) is NULL, then the interval is invalid and is ignored.

Examples

[1] Calculate the number of seconds since 1970-01-01 00:00:00.000 by using a specified factory calendar and using the REMAP_TIMESTAMPS function.

Query

Column1

REMAP_TIMESTAMPS (
    "activity"."time" ,
    SECONDS ,
    FACTORY_CALENDAR ( "calendar"."start" , "calendar"."end" )
)

Input

Output

activity

id : INT

case : INT

act : STRING

time : DATE

1

1

'a'

Thu Jan 01 1970 00:00:00.000

2

1

'b'

Thu Jan 01 1970 00:01:00.000

3

1

'c'

Thu Jan 01 1970 00:02:00.000

4

1

'd'

Thu Jan 01 1970 00:03:00.000

5

2

'a'

Thu Jan 01 1970 00:01:00.000

6

2

'b'

Thu Jan 01 1970 00:02:00.000

7

2

'c'

Thu Jan 01 1970 00:04:00.000

8

2

'd'

Thu Jan 01 1970 00:05:00.000

9

3

'a'

Thu Jan 01 1970 00:00:00.000

10

3

'b'

Thu Jan 01 1970 00:01:00.000

11

3

'b'

Thu Jan 01 1970 00:02:00.000

12

3

'b'

Thu Jan 01 1970 00:03:00.000

calendar

id : INT

start : DATE

end : DATE

1

Thu Jan 01 1970 00:00:00.000

Thu Jan 01 1970 00:01:00.000

2

Thu Jan 01 1970 00:02:00.000

Thu Jan 01 1970 00:03:00.000

3

Thu Jan 01 1970 00:04:00.000

Thu Jan 01 1970 00:05:00.000

4

Thu Jan 01 1970 00:06:00.000

Thu Jan 01 1970 00:07:00.000

Result

Column1 : INT

0

60

60

120

60

60

120

180

0

60

60

120

[2] Calculate the number of seconds since 1970-01-01 00:00:00.000 by using a specified factory calendar with a filter applied (again, using the REMAP_TIMESTAMPS function).

Query

Column1

REMAP_TIMESTAMPS (
    "activity"."time" ,
    SECONDS ,
    FACTORY_CALENDAR ( "calendar"."start" , "calendar"."end" , "calendar"."filter" = 3 )
)

Input

Output

activity

id : INT

case : INT

act : STRING

time : DATE

1

1

'a'

Thu Jan 01 1970 00:00:00.000

2

1

'b'

Thu Jan 01 1970 00:01:00.000

3

1

'c'

Thu Jan 01 1970 00:02:00.000

4

1

'd'

Thu Jan 01 1970 00:03:00.000

5

2

'a'

Wed Dec 31 1969 23:59:00.000

6

2

'b'

Thu Jan 01 1970 00:02:00.000

7

2

'c'

null

8

2

'd'

Thu Jan 01 1970 00:05:00.000

9

3

'a'

Thu Jan 01 1970 00:00:00.000

10

3

'b'

Thu Jan 01 1970 00:01:00.000

11

3

'b'

Thu Jan 01 1970 00:02:00.000

12

3

'b'

Thu Jan 01 1970 00:03:00.000

calendar

id : INT

filter : INT

start : DATE

end : DATE

1

1

Thu Jan 01 1970 00:00:00.000

Thu Jan 01 1970 00:01:00.000

2

1

null

Thu Jan 01 1970 00:03:00.000

3

1

Thu Jan 01 1970 00:04:00.000

null

4

2

Thu Jan 01 1970 00:04:00.000

Thu Jan 01 1970 00:03:00.000

Result

Column1 : INT

0

0

0

0

0

0

null

0

0

0

0

0

[3] Invalid date intervals in the factory calendar are ignored.

Query

Column1

REMAP_TIMESTAMPS (
    "activity"."time" ,
    SECONDS ,
    FACTORY_CALENDAR ( "calendar"."start" , "calendar"."end" , "calendar"."filter" = 2 )
)

Input

Output

activity

id : INT

case : INT

act : STRING

time : DATE

1

1

'a'

Thu Jan 01 1970 00:00:00.000

2

1

'b'

Thu Jan 01 1970 00:01:00.000

3

1

'c'

Thu Jan 01 1970 00:02:00.000

4

1

'd'

Thu Jan 01 1970 00:03:00.000

5

2

'a'

Wed Dec 31 1969 23:59:00.000

6

2

'b'

Thu Jan 01 1970 00:02:00.000

7

2

'c'

null

8

2

'd'

Thu Jan 01 1970 00:05:00.000

9

3

'a'

Thu Jan 01 1970 00:00:00.000

10

3

'b'

Thu Jan 01 1970 00:01:00.000

11

3

'b'

Thu Jan 01 1970 00:02:00.000

12

3

'b'

Thu Jan 01 1970 00:03:00.000

calendar

id : INT

filter : INT

start : DATE

end : DATE

1

1

Thu Jan 01 1970 00:00:00.000

Thu Jan 01 1970 00:01:00.000

2

1

null

Thu Jan 01 1970 00:03:00.000

3

1

Thu Jan 01 1970 00:04:00.000

null

4

2

Thu Jan 01 1970 00:04:00.000

Thu Jan 01 1970 00:03:00.000

Result

Column1 : INT

0

0

0

0

0

0

null

0

0

0

0

0

Note

Encountered the following invalid date interval: [1970-01-01T00:04:00 , 1970-01-01T00:03:00] (the begin of any interval must not be larger than its end). Therefore, the interval is ignored.

[4] FACTORY_CALENDAR(calendar_table) syntax. This is used in REMAP_TIMESTAMPS along with a Calendar IDs column. Factory Calendar is created for different identifiers present in the Calendar IDs column.

Query

Column1

REMAP_TIMESTAMPS (
    "TimestampsTable"."Timestamp" ,
    DAYS ,
    FACTORY_CALENDAR ( "FactoryCalendarTable" ) ,
    "TimestampsTable"."CalendarID"
)

Input

Output

FactoryCalendarTable

IDENT : STRING

START : DATE

END : DATE

'DE'

Thu Jan 01 1970 00:00:00.000

Fri Jan 02 1970 01:00:00.000

'USA'

Thu Jan 01 1970 00:00:00.000

Fri Jan 02 1970 01:00:00.000

'JPN'

Thu Jan 01 1970 00:00:00.000

Fri Jan 02 1970 01:00:00.000

TimestampsTable

CalendarID : STRING

Timestamp : DATE

'DE'

Sat Jul 06 2019 00:00:00.000

'DE'

Wed Jan 07 1970 15:30:00.000

'JPN'

Sat Jul 06 2019 00:00:00.000

'USA'

Wed Jan 07 1970 15:30:00.000

'JPN'

Wed Jan 07 1970 15:30:00.000

Result

Column1 : INT

1

1

1

1

1

See also: