Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

INTERSECT

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

Output

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

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

Output

TFACS

IDENT : STRING

JAHR : INT

MON01 : STRING

MON02 : STRING

MON03 : STRING

MON04 : STRING

MON05 : STRING

MON06 : STRING

MON07 : STRING

MON08 : STRING

MON09 : STRING

MON10 : STRING

MON11 : STRING

MON12 : 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

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

Output

TFACS

IDENT : STRING

JAHR : INT

MON01 : STRING

MON02 : STRING

MON03 : STRING

MON04 : STRING

MON05 : STRING

MON06 : STRING

MON07 : STRING

MON08 : STRING

MON09 : STRING

MON10 : STRING

MON11 : STRING

MON12 : 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 : 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

Table2

Timestamps : DATE

Ident : STRING

Thu Jun 06 2019 00:00:00.000

'DE'

Thu Jun 06 2019 00:00:00.000

'JPN'

Result

Column1 : INT

0

0

Warning

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: