Applies to:  CELONIS 4.7 

Description

A call to INTERSECT takes two arbitrary calendars as input and produces a new intersected calendar (its type depends on the input calendars).

The currently supported calendars are

At all places where a calendar is expected (e.g., in REMAP_TIMESTAMPS), a calendar intersection can be used as well (as the intersection produces a new calendar as output).

Syntax

INTERSECT ( lhs_calendar, rhs_calendar )

Examples


[1] Here we use REMAP_TIMESTAMPS to calculate the number of passed minutes since 1970-01-01 for several dates in the 'Timestamps' column. The calendar specified is a calendar intersection between two weekday calendars. If one weekday calendar does not cover a certain weekday (e.g., the second weekday calendar in the intersection does not cover 'WEDNESDAY'), it will also not be covered by their intersection. For the shifts the intersection algorithm takes max(lhs_begin,rhs_begin) as the new begin of the shift and min(lhs_end,rhs_end) as the new end of the shift (where 'lhs' refers to the left-hand side calendar of the intersection input and 'rhs' to the right-hand side).

Query
Column1
REMAP_TIMESTAMPS (
    "Table1"."Timestamps" ,
    MINUTES ,
    INTERSECT (
        WEEKDAY_CALENDAR (
            MONDAY 00:00 - 10:00
            TUESDAY 05:33 - 13:20
            WEDNESDAY 05:00 - 11:06
            THURSDAY 08:20 - 20:00
            FRIDAY 00:00 - 16:39
        ) ,
        WEEKDAY_CALENDAR (
            MONDAY 00:00 - 05:00
            TUESDAY 06:40 - 14:26
            THURSDAY 16:40 - 21:40
            FRIDAY 00:00 - 16:39
            SATURDAY 00:00 - 24:00
            SUNDAY 12:34 - 23:45
        )
    )
)
Input
Table1
Timestamps : DATE
Thu Jan 01 1970 00:00:00.000
Thu Jan 01 1970 16:40:00.000
Thu Jan 01 1970 16:40:01.000
Thu Jan 01 1970 20:00:00.000
Thu Jan 01 1970 20:16:40.000
Fri Jan 02 1970 00:00:00.000
Fri Jan 02 1970 00:00:01.000
Fri Jan 02 1970 16:39:00.000
Fri Jan 02 1970 16:39:01.000
Sun Jan 04 1970 16:39:01.000
Mon Jan 05 1970 00:00:01.000
Wed Jan 14 1970 00:00:01.000
Tue Feb 29 1972 06:40:00.000
Tue Dec 30 1969 06:40:00.000
Tue Dec 30 1969 13:19:59.999
Tue Dec 30 1969 13:20:00.000
Wed Jan 01 1969 00:00:00.000
Output
Result
Column1 : INT
0
0
0
200
200
200
200
1199
1199
1199
1199
3798
214187
-400
0
0
-98748



[2] Here we use REMAP_TIMESTAMPS to calculate the number of passed hours since 1970-01-01 for several dates in the 'Timestamps' column. The calendar specified is a calendar intersection between a workday calendar (configured for the years 2018 and 2019) and a weekday calendar with shifts. The result of the calendar intersection will be a workday calendar which has the corresponding shifts of the weekday calendar. Note that the weekday calendar does not cover the weekday WEDNESDAY and thus all wednesdays are marked as invalid workdays in the workday calendar. All other weekdays are covered by the weekday calendar with a shift from 09:00-17:00. However, because weekends are not valid workdays in the workday calendar, these are ignored in the calendar computations (together with all public holidays such as the first of January) although the weekends are configured in the weekday calendar. Also, all days before 2018-01-01 are ignored as the first year covered by the workday calendar is the year 2018. More information for the inner workings of the WORKDAY_CALENDAR can be found in its documentation.

The following example starts with the second of January 2018 which is a Tuesday. It is both a valid workday according to the workday calendar and also covered by the weekday calendar for the time from 09:00 until 17:00. In the example, we can see that the third of January 2018 is ignored because it was a Wednesday and is thus not covered by the weekday calendar. The last date in the example (i.e., 2018-01-08 16:30:00) is a Monday. The following days are ignored in the calculations:

  • Monday, 2018-01-01: Public holiday according to the workday calendar configuration.
  • Wednesday, 2018-01-03: Not covered by the weekday calendar configuration.
  • Saturday, 2018-01-06 and Sunday, 2018-01-07: Days on the weekend are no workdays according to the workday calendar configuration.
The following days and shifts are considered in the calendar computations:
  • Tuesday, Thursday, Friday (2018-01-02, 2018-01-04, 2018-01-05 respectively): From 09:00-17:00 (with a total of 24 hours).
  • Monday, 2018-01-08: From 09:00-16:30 (with 7.5 hours passed).
In total, this makes 24 hours + 7.5 hours = 31.5 hours which will be rounded down to 31 hours.


Query
Column1
REMAP_TIMESTAMPS (
    Table1.Timestamps ,
    HOURS ,
    INTERSECT (
        WORKDAY_CALENDAR ( "TFACS" , 'BAY' ) ,
        WEEKDAY_CALENDAR (
            MONDAY 09:00 - 17:00
            TUESDAY 09:00 - 17:00
            THURSDAY 09:00 - 17:00
            FRIDAY 09:00 - 17:00
            SATURDAY 09:00 - 17:00
            SUNDAY 09:00 - 17:00
        )
    )
)
Input
TFACS
IDENT : STRINGJAHR : INTMON01 : STRINGMON02 : STRINGMON03 : STRINGMON04 : STRINGMON05 : STRINGMON06 : STRINGMON07 : STRINGMON08 : STRINGMON09 : STRINGMON10 : STRINGMON11 : STRINGMON12 : STRING
'BAY'
2018
'0111100111110011111001111100111''1100111110011111001111100111''1100111110011111001111100111100''001111001111100111110011111001''0111001110100111110001111001110''100111110011111001111100111110''0111110011111001111100111110011''1110011111001101100111110011111''001111100111110011111001111100''1101100111110011111001111100111''010011111001111100111110011111''0011111001111100111110010011001'
'BAY'
2019
'0111001111100111110011111001111''1001111100111110011111001111''1001111000111110011111001111100''111110011111001111000011110011''0110011111001111100111110011101''001111100011110011101001111100''1111100111110011111001111100111''1100111110011101001111100111110''011111001111100111110011111001''1101001111100111110011111001111''000111110011111001101100111110''0111110011111001111100110010011'
Table1
Timestamps : DATE
Tue Jan 02 2018 08:00:00.000
Tue Jan 02 2018 09:00:00.000
Tue Jan 02 2018 10:00:00.000
Tue Jan 02 2018 10:59:59.999
Tue Jan 02 2018 11:00:00.000
Tue Jan 02 2018 16:00:00.000
Tue Jan 02 2018 17:00:00.000
Tue Jan 02 2018 18:00:00.000
Wed Jan 03 2018 18:00:00.000
Mon Jan 08 2018 16:30:00.000
Output
Result
Column1 : INT
0
0
1
1
2
7
8
8
8
31



[3] Here we use REMAP_TIMESTAMPS to calculate the number of passed minutes since 1970-01-01 for several dates in the 'Timestamps' column. The calendar specified is a calendar intersection between a workday and a factory calendar. The result of the intersection would be a factory calendar.

Query
Column1
REMAP_TIMESTAMPS (
    Table2.Timestamps ,
    HOURS ,
    INTERSECT ( WORKDAY_CALENDAR ( "TFACS" ) , FACTORY_CALENDAR ( "Table1" ) ) ,
    "Table2"."Ident"
)
Input
TFACS
IDENT : STRINGJAHR : INTMON01 : STRINGMON02 : STRINGMON03 : STRINGMON04 : STRINGMON05 : STRINGMON06 : STRINGMON07 : STRINGMON08 : STRINGMON09 : STRINGMON10 : STRINGMON11 : STRINGMON12 : STRING
'BAY'
1904
'0001101100111110011111001111100''11111001111100111110011111001''1111001111100111110011111001111''000011110011111001111100111110''0111110011101001111100011110011''101001111100111110011111001111''1001111100111110011111001111100''1111100111110001111001111100111''110011111001111100111110011111''0011111001111100111110011111001''011100111110011111001111100111''1100111110011111001111100011110'
'DE'
1907
'0111001111100111110011111001111''1001111100111110011111001111''1001111100111110011111001111000''011110011111001111100111110011''0110011101001111100011110011101''001111100111110011111001111100''1111100111110011111001111100111''1100111110011101001111100111110''011111001111100111110011111001''1111001111100111110011111001111''000111110011111001111100111110''0111110011111001111100110010011'
'JPN'
1974
'0111001111100111110011111001111''1001111100111110011111001111''1001111100111110011111001111100''111110011110000111100111110011''0110011111001111100111010011111''000111100111010001111001111100''1111100111110011111001111100111''1100111110011101001111100111110''011111100111110111110011111001''1111001111100111110011111001111''000111110011111001101100111110''0111110011111001111100110010011'
'BAY'
2018
'0111100111110011111001111100111''1100111110011111001111100111''1100111110011111001111100111100''001111001111100111110011111001''0111001110100111110001111001110''100111110011111001111100111110''0111110011111001111100111110011''1110011111001101100111110011111''001111100111110011111001111100''1101100111110011111001111100111''010011111001111100111110011111''0011111001111100111110010011001'
'DE'
2019
'0111001111100111110011111001111''1001111100111110011111001111''1001111000111110011111001111100''111110011111001111000011110011''0110011111001111100111110011101''001111100011110011101001111100''1111100111110011111001111100111''1100111110011101001111100111110''011111001111100111110011111001''1101001111100111110011111001111''000111110011111001101100111110''0111110011111001111100110010011'
'JPN'
1904
'0001101100111110011111001111100''11111001111100111110011111001''1111001111100111110011111001111''000011110011111001111100111110''0111110011101001111100011110011''101001111100111110011111001111''1001111100111110011111001111100''1111100111110001111001111100111''110011111001111100111110011111''0011111001111100111110011111001''011100111110011111001111100111''1100111110011111001111100011110'
'BAY'
1907
'0111001111100111110011111001111''1001111100111110011111001111''1001111100111110011111001111000''011110011111001111100111110011''0110011101001111100011110011101''001111100111110011111001111100''1111100111110011111001111100111''1100111110011101001111100111110''011111001111100111110011111001''1111001111100111110011111001111''000111110011111001111100111110''0111110011111001111100110010011'
'DE'
1974
'0111001111100111110011111001111''1001111100111110011111001111''1001111100111110011111001111100''111110011110000111100111110011''0110011111001111100111010011111''000111100111010001111001111100''1111100111110011111001111100111''1100111110011101001111100111110''011111100111110111110011111001''1111001111100111110011111001111''000111110011111001101100111110''0111110011111001111100110010011'
'JPN'
2018
'0111100111110011111001111100111''1100111110011111001111100111''1100111110011111001111100111100''001111001111100111110011111001''0111001110100111110001111001110''100111110011111001111100111110''0111110011111001111100111110011''1110011111001101100111110011111''001111100111110011111001111100''1101100111110011111001111100111''010011111001111100111110011111''0011111001111100111110010011001'
'BAY'
2019
'0111001111100111110011111001111''1001111100111110011111001111''1001111000111110011111001111100''111110011111001111000011110011''0110011111001111100111110011101''001111100011110011101001111100''1111100111110011111001111100111''1100111110011101001111100111110''011111001111100111110011111001''1101001111100111110011111001111''000111110011111001101100111110''0111110011111001111100110010011'
'DE'
1904
'0001101100111110011111001111100''11111001111100111110011111001''1111001111100111110011111001111''000011110011111001111100111110''0111110011101001111100011110011''101001111100111110011111001111''1001111100111110011111001111100''1111100111110001111001111100111''110011111001111100111110011111''0011111001111100111110011111001''011100111110011111001111100111''1100111110011111001111100011110'
'JPN'
1907
'0111001111100111110011111001111''1001111100111110011111001111''1001111100111110011111001111000''011110011111001111100111110011''0110011101001111100011110011101''001111100111110011111001111100''1111100111110011111001111100111''1100111110011101001111100111110''011111001111100111110011111001''1111001111100111110011111001111''000111110011111001111100111110''0111110011111001111100110010011'
'BAY'
1974
'0111001111100111110011111001111''1001111100111110011111001111''1001111100111110011111001111100''111110011110000111100111110011''0110011111001111100111010011111''000111100111010001111001111100''1111100111110011111001111100111''1100111110011101001111100111110''011111100111110111110011111001''1111001111100111110011111001111''000111110011111001101100111110''0111110011111001111100110010011'
'DE'
2018
'0111100111110011111001111100111''1100111110011111001111100111''1100111110011111001111100111100''001111001111100111110011111001''0111001110100111110001111001110''100111110011111001111100111110''0111110011111001111100111110011''1110011111001101100111110011111''001111100111110011111001111100''1101100111110011111001111100111''010011111001111100111110011111''0011111001111100111110010011001'
'JPN'
2019
'0111001111100111110011111001111''1001111100111110011111001111''1001111000111110011111001111100''111110011111001111000011110011''0110011111001111100111110011101''001111100011110011101001111100''1111100111110011111001111100111''1100111110011101001111100111110''011111001111100111110011111001''1101001111100111110011111001111''000111110011111001101100111110''0111110011111001111100110010011'
Table1
IDENT : STRINGSTART : DATEEND : 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
Table2
Timestamps : DATEIdent : STRING
Thu Jun 06 2019 00:00:00.000
'DE'
Thu Jun 06 2019 00:00:00.000
'JPN'
Output
Result
Column1 : INT
0
0

The provided workday calendars contains one or more gaps between its start and end year. Please note, that all the missing years (i.e., the gaps) are ignored for calendar calculations. Affected IDENT values: DE, JPN


See also:

  • No labels