Page tree
Skip to end of metadata
Go to start of metadata

Summary

This operator takes a Durations table (see Types of Tables) as input table and compares its intervals to a set of intervals defined in another data node (interval node, see below). The interval node specifies valid intervals for each day of the week. 

Its result table compares these two tables in different ways, depending on the settings. It can further allocate intervals of the input table to intervals of the interval node according to identifiers.

Example

Situation

We want to know how our work time records (input table) overlap with our core working hours (interval node). We have two groups of employees, office staff and production workers.

The table below is our input table containing the work time records (data node A01 in project file below). It indicates the employee group in column A and the actual working intervals in columns B and C. Column D is not required, it is added for a better understanding of later examples.


The next table is our interval table containing the core working hours (data node I01 in project file below).

Note: The columns "From" ("Von") and "To" ("Bis") only specify times, not dates. Their creation date is added per default because the Date/Time format requires a date. Hence, in this table, the dates "06.04.2017" are ignored, only the times are relevant.

The columns C to J indicate on which days of the week the intervals are valid. Column K indicates for which employee group the intervals are valid.

Settings

We set the columns A of the input table and K of the interval table as identifiers to allocate the data rows to the employee groups (operation in data node C01).

We choose "Overlapping intervals (first)" for the parameter "Result" (German "Überlappende Intervalle" for "Ergebnis).

Further, we specify the interval node "I01 Intervals" with its columns "From" ("Spalte Intervallzeit von"), "To" ("Spalte Intervallzeit bis") , and the columns indicating the validities per weekday. ("Spalten Wochentage").

Result

The result table contains one row for each overlap between an interval of the input table with an interval of the interval table with matching identifiers (data node C01).

All fields of these rows of both tables are repeated. Additionally, the two columns "Result From" and "Result To" are added to indicate the overlap.



Project File



 Interval node and holiday node

The interval node is a different data node in the same project. It defines time intervals with a validity on specific days of the week and for holidays. The time intervals of the input table are compared to the intervals valid on the respective day.

The interval node must contain the following columns:

Name conventionDescriptionTypeOptional/requiredRemark
FromStart time of the intervalTimeRequiredOnly the time is specified and relevant, but the creation date is added automatically. The date is ignored for all functionality of this operator.
ToEnd time of the intervalTimeRequiredOnly the time is specified and relevant, but the creation date is added automatically. The date is ignored for all functionality of this operator.
MoValidity of interval on MondaysBooleanRequired
TueValidity of interval on TuesdaysBooleanRequired
WedValidity of interval on WednesdaysBooleanRequired
ThuValidity of interval on ThursdaysBooleanRequired
FriValidity of interval on FridaysBooleanRequired
SatValidity of interval on SaturdaysBooleanRequired
SunValidity of interval on SundaysBooleanRequired
HolidayValidity of interval on holidaysBooleanOptionalHolidays are specified in a separate data node.

The order of the Boolean columns is compulsory.

The time in the "From" column can be later than or equal to the time in the "To" column. This way, intervals spanning over two days can be defined. In these cases, different interpretations are possible. The options can be chosen by setting the parameters "Same From and To time means" and "Interval 23:00-06:00 means", respectively.

Further, the interval table may contain one or more columns of identifiers. They can be used to distinguish, e.g., working times for different groups of employees.

Holidays are listed explicitly in a different data node. This data node's result table must contain a Date column. The data node and the date column in the data node are specified in the operator's parameters "Holidays data node" and "Holiday column", respectively.

 Classification of interval relations

An interval specified in the input table can have one or more of the following relations to an interval specified in the interval table.

English nameGerman nameDescriptionExample
Overlapping intervalsÜberlappende Intervalle

The input interval overlaps with an interval from the interval table

Indicates the overlap.

Interval 17:00 to 19:00 from the input table overlaps with the interval 14:00 to 18:00 from the interval table. The overlap is from 17:00 to 18:00.

Missing intervalsFehlende Intervalle

The input interval overlaps only with parts of an interval from the interval table or it does not overlap at all with an interval of the interval table.

Indicates (part of) the not-overlapped interval from the interval table.

In row 1, the input interval 10:00 to 13:00 does not overlap the interval 05:00 to 09:00 from the interval table. The whole interval 05:00 to 09:00 is missed by the input interval.

In row 5, the input interval 04:00 to 08:00 overlaps only parts of interval 05:00 to 09:00 from the interval table. It misses 08:00 to 09:00

Times not included in intervalsZeiten kommen in Intervallen nicht vor

The input interval overlaps only with parts of an interval from the interval table or it does not overlap at all with an interval of the interval table.

Indicates (part of) the not-overlapped interval from the input table.

In row 3, the interval 4:00 to 5:00, which is part of the input interval 4:00 to 8:00 does not overlap with any interval from the interval table.


 Settings

This operator computes overlaps between a Durations table (see Types of Tables) and a set of intervals defined in the interval node (see above). The settings define the (parts of the) input table, the (parts of the) interval table and the holiday table, and the columns and calculations in the result table.

Columns of input table


ParameterValueOpt.DescriptionExample
Identifier

System.Object

opt.One or more columns that contain identifiers of the data rows. If identifiers are defined here, they have to match the identifiers in the interval table (see parameter "Identified interval node" in the table below).The result table then allocates the intervals of the input table according to the intervals in the interval node with the same identifier(s).-
Column from

System.DateTime

-Date/Time column containing the start time and date of an interval.-
Column to

System.DateTime

-Date/Time column containing the end time and date of an interval.-

Parameters


ParameterValueOpt.DescriptionExample
Result

System.String

  • Overlapping intervals (first)
  • Overlapping intervals (all)
  • Missing intervals (view per row)
  • Missing intervals (all)
  • Times not included in intervals
  • Classification of times
-Information to be displayed in the result table. See below for a description of each of the options.-
Time intervals data node

System.Int32

-Reference to the interval node, see description above.-
Identified interval node

System.String

opt.One or more columns containing identifiers in the interval node. They have to match the identifiers defined in the specification of the relevant columns of the input table (see table above).-
Interval Time From column

System.String

-Date/Time column containing the start time and date of the intervals in the interval node.-
Interval Time To column

System.String

-Date/Time column containing the end time and date of the intervals in the interval node.-
Weekdays column

System.String

-

Seven boolean columns of the interval node defining the validity of intevers for weekdays (see description of interval node above), in the order Mo, Tue, Wed, Thu, Fri, Sat, Sun. Optionally one more column added to the end defining validity of the interval on a holiday.

-
Same From and To time means

System.String

  • 24 hours
  • 0 hours (ignore interval)
-

Intervals that contain the same time in their "From" and "To" column can be interpreted in different ways.

24 hours: The interval spans to the following day.

0 hours: The interval is ignored.

-
Holidays data node

System.Int32

opt.Reference to a data node containing a list of holidays.-
Holiday column

System.String

opt.Date column of the holiday node that contains the holidays.-
Interval 23:00-06:00 means

System.String

  • 00:00–06:00 and 23:00-24:00 same day
  • 23:00-06:00 following day
-

The interval table may contain data rows that show an earlier time in their "To" column than in their "From" column. They can be interpreted in different ways.

00:00–06:00 and 23:00–24:00 same day: The interval is split into two intervals, one at the beginning, and the other at the end of the same day.

23:00–06:00 following day: The interval spans to the following day.

(This distinction is important when intervals are only valid on some weekdays. If they are valid on all weekdays, the result will be the same.)

-
Generate blank row if nothing has been found

System.Boolean

-

If checked, a table containing a single row is returned instead of an empty table.

This row contains NULL in numerical columns and date columns and the text defined in parameter "Text fields content for blank line" in text columns.

-
Text fields content for blank line

System.String

opt.

The text to be inserted in text columns if parameter "Generate blank row if nothing has been found" is checked.

-

 Result options and result table

Independently of the result option, the result table contains

  • all columns of the input table in their order,
  • all columns of the interval table in their order,
  • one DateTime column "Result From", and
  • one DateTime column " Result To"

in this order.

The value of the parameter "Result" decides which rows of the input table are contained in the result table, with which rows of the interval table they are combined, and the information shown in the "Result From" and "Result To" columns.

Overlapping (first)

These option computes the intervals and parts of intervals where data from the input table and data from the interval table overlap.

The result table contains one row for each overlap between the input intervals and the intervals of the interval table.

It contains more than one row for one input interval, if this interval overlaps with more than one interval of the interval table.

However, if an input interval overlaps with more than one interval of the interval table and these intervals of the interval tables have the same "From" time and the same "To" time, then only the overlap with the first interval, with respect to the ordering of the rows in the interval table, is contained in the result table.

The result table copies all fields of the respective row in the input table and all fields of the row in the interval table that contains the interval it overlaps with. The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the overlap.

See also "Overlapping intervals" in the classification of interval relations above. Table below shows result table of data node C02 of project file.

Overlapping (all)

These option computes the intervals and parts of intervals where data from the input table and data from the interval table overlap.

The result table contains one row for each overlap between the input intervals and the intervals of the interval table.

It contains more than one row for one input interval, if this interval overlaps with more than one interval of the interval table, regardless of the double intervals having the same "From" and "To" times (this is the difference to the option Overlapping (first)).

The result table copies all fields of the respective row in the input table and all fields of the row in the interval table that contains the interval it overlaps with. The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the overlap.

See also "Overlapping intervals" in the classification of interval relations above. Table below shows result table of data node C03 of project file. Note row 2 of this table, which is not included in the table computed with option "Overlapping (first)" above.


Missing intervals (view by row)

This option computes the intervals and parts of intervals of the interval table not overlapped by intervals of the input table. It considers for each entry in the input table all intervals valid for that day.

The result table contains one row for each input interval and interval of the interval table valid for the respective day and not at all or not completely overlapped by the input interval. It hence contains more than one row for one input interval if there is more than one interval in the interval table valid on the day which the input interval does not overlap.

The result table copies all fields of the respective rows of both the input table and the interval table. The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the (part of the) interval that is not overlapped by the input data on the respective day.

See also "Missing intervals" in the classification of interval relations above. Table below shows result table of data node C04 of project file.


Missing intervals (all)

This option computes intervals and parts of intervals of the interval table not overlapped by intervals of the input table, like option "Missing intervals (view by row). The difference is that it considers for each day between and including the earliest and the latest day of the input table each interval valid on that day.

The result table contains one row for each interval of the interval table for each day for which it is valid between the earliest and the latest day of the input table where it is not completely covered by an interval of the input table.

The result table fills all fields of the input table with "-" and copies those of the interval table. The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the (part of the) interval that is not overlapped by the input interval on the respective day.

See also "Missing intervals" in the classification of interval relations above. Table below shows result table of data node C05 of project file.


Times not included in intervals

This option computes intervals of the input table not overlapped by intervals of the interval table (the inverse of option "Missing intervals (view by row)").

The result table contains one row for each input interval that is not fully overlapped by an interval of the interval table. It hence contains at most one row for each row of the input table. It copies the rows of the input table and fills all fields of the interval table with "-". The "Result From" and "Result To" columns contain the start time and the end time, respectively, of the (part of the) input interval not overlapped by an interval of the interval table.

See also "Times not included in intervals" in the classification of interval relations above. Table below shows result table of data node C06 of project file.

Classification of times

This option considers each row of the input intervals and classifies its interval according to the classifications described above ("Classification of interval relations").

The result table contains one row for each input interval and its classification(s). Hence, if an input interval falls into more than one classification, then the result table contains more than one row for this interval. The "Result From" and "Result To" columns contain the start and the end time of different intervals depending on its classification:

  • Overlapping intervals (Überlappende Intervalle): Start time and end time of the overlap inside the interval of the interval table.
  • Missing intervals (Fehlende Intervalle): Start time and end time of the non-overlap inside the interval of the interval table.
  • Times not included in intervals (Zeiten kommen in Intervallen nicht vor): Start time and end time of the non-overlap inside the input interval.

Table below shows result table of data node C07 of project file.





 Examples

Introduction

The following examples refer to the following interval table, holiday table, and input table, unless indicated otherwise. The examples are contained in this project and can be imported into TIS. Confluence missing%2foverlapping op.gzip

Interval table (data node "Interval tables/I01 Intervals", dates blanked out for better readability as they are not relevant)

Holiday table (data node "Interval tables/I02 Holidays")


Input table (data node "Raw data/A02 simple with missing days"

Overlap (first)

Data nodeSettingsResultRemarks
Examples/E02

 

 

"Result From" and "Result To" fields indicate the overlap between the columns B and C (input interval) and columns E and F (interval from interval table).

Overlap (all)

Data nodeSettingsResultRemarks
Examples/E03

"Result From" and "Result To" fields indicate the overlap between the columns B and C (input interval) and columns E and F (interval from interval table).

Rows 1 and 2 contain an interval from the interval table with the same "From" and "To" times, both valid on Wednesday. Other than for option "Overlap (first)", here they are both included in the result table.

Missing intervals (view per row)

Data nodeSettingsResultRemarks
Examples/E04

"Result From" and "Result To" fields indicate (parts of) the interval from columns E and F (interval table) not overlapped by the interval from columns B and C (input table).

Missing intervals (all)

Data nodeSettingsResultRemarks
Examples/E05

"Result From" and "Result To" fields indicate (parts of) the interval from columns E and F (interval table) not overlapped by any input interval.

Data is shown for each day from the earliest day (07.02.2017) to the latest day (09.02.2017) of the input table.

Times not included in intervals

Data nodeSettingsResultRemarks
Examples/E06

"Result From" and "Result To" fields indicate (parts of ) the interval from columns B and C (input table) not overlapped by any interval from the interval table.

Classification of times

Data nodeSettingsResultRemarks
Examples/E07

"Result From" and "Result To" fields indicate (parts of) either the interval from columns B and C (input table) or columns E and F (interval table), depending on the classification of the interval (see "Classification of interval relations" above).

Definition of holidays

Data nodeSettingsResultRemarks
Examples/E08

This data node is the same as E07 except that a node defining holidays is included in the settings (node Intervals/I02).

The result is the same except that row 1 of E07 is missing in E08 as this interval is not defined for holidays and 07.02.2017 is defined as holiday in node I02.

Interval over midnight

Here, the interval node Intervals/I03 is used. It contains only the following interval.

Depending on the value of parameter "Interval 23:00-06:00 means", the overlappings are computed in different ways.

Data nodeSettingsResultRemarks
Examples/E09

The interval of the interval table only counts once and only overlaps with input intervals that start on the first day are considered.

Note that in row 4, the time between 04:00 and 06:00 is not considered an overlap!

Examples/E10

Here, the interval of the interval table is split into two intervals, one ending and one starting at midnight, both on the same day.

Note that in row 6, the overlap between 04:00 and 06:00 is considered.




 Troubleshooting
ProblemFrequent CauseSolutions
... 

...

Related topics

  • ...