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.
|
[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).
|
[3] Invalid date intervals in the factory calendar are ignored.
|
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.
|