# Identify overlapped items **URL:** \ **Category:** KNIME Analytics Platform\ **Created:** [May 4, 2022, 5:34pm UTC](https://forum.knime.com/t/identify-overlapped-items/42495 "2022-05-04T17:34:18Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![LuisMP](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/l/b19c9b/32.png) [@LuisMP](https://forum.knime.com/u/LuisMP)\ **Post date:** [May 4, 2022, 5:34pm UTC](https://forum.knime.com/t/identify-overlapped-items/42495/1 "2022-05-04T17:34:19Z")
Hi! I was wondering if there’s a way to identify overlapped values on two different columns. To give a bit of context, I have a table that looks somewhat like this ![image](https://forum-cdn.knime.com/uploads/default/original/3X/6/d/6d091d9b868e6da259bc31912e69e454d37fbdf4.png) So, the thing is that for plant 123 the order B starts before the order A finishes and that type of overlapping is what I’m tryng to identify, but the data is huge and it would take my forever. Is there a way to make this easier? ---
**Author:** ![elsamuel](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/e/b4bc9f/32.png) [@elsamuel](https://forum.knime.com/u/elsamuel)\ **Post date:** [May 4, 2022, 6:38pm UTC](https://forum.knime.com/t/identify-overlapped-items/42495/2 "2022-05-04T18:38:08Z")
One idea is 1. introduce a lag column based on Finish Time 2. use a Date&Time difference node to calculate the difference between Start Time and the lagged Finish Time 3. convert the difference to a number, 4. if this is positive, flag the row as having an overlap Carry all of this out inside a Group Loop so that the analysis is done Plant by Plant. ![image](https://forum-cdn.knime.com/uploads/default/original/3X/9/c/9c62b234d945d9d413268599b80413933b6f747d.png) If you have different dates involved, then you’ll have to adjust your grouping strategy. ---
**Author:** ![gonhaddock](https://forum-cdn.knime.com/user_avatar/forum.knime.com/gonhaddock/32/35425_2.png) [@gonhaddock](https://forum.knime.com/u/gonhaddock)\ **Post date:** [May 4, 2022, 6:52pm UTC](https://forum.knime.com/t/identify-overlapped-items/42495/3 "2022-05-04T18:52:28Z")
Hi @LuisMP I did a different approach, identifying overlapping time in minutes. Negative overlaps stand for overlapping at the beginning of the interval, in the other hand, positive overlaps happen at the end of the interval. ![image](https://forum-cdn.knime.com/uploads/default/original/3X/c/3/c3ed6de3a7eb30212bf30c73b4daf426e7a7f5a0.png) > **[Data Wrangling \_ Identify Time Overlap – gonhaddock](https://hub.knime.com/gonhaddock/spaces/Public/latest/Data%20Wrangling%20_%20Identify%20Time%20Overlap~wHjT22Vo2WtSWuK9)** > > There has been no description set for this workflow's metadata. ![image](https://forum-cdn.knime.com/uploads/default/original/3X/2/3/230b1dbd15befad221c9c4d0ded07f9faf85a7f3.jpeg) The workflow works for Date and Time format. BR ---
**Author:** ![takbb](https://forum-cdn.knime.com/user_avatar/forum.knime.com/takbb/32/57077_2.png) [@takbb](https://forum.knime.com/u/takbb)\ **Post date:** [May 4, 2022, 8:50pm UTC](https://forum.knime.com/t/identify-overlapped-items/42495/4 "2022-05-04T20:50:24Z")
Hi @LuisMP, Demonstrating that KNIME often provides multiple solutions, here is another take on it. As your times don’t include dates, my initial stab produced incorrect results where an end time crosses midnight and so _appears_ to be earlier than the start time. So I chose to turn the times into numbers so that I could adjust end times upwards if they were earlier. Of course if a process takes more than 24 hours this could still pose a problem! Based on your example, I assumed you were looking only for overlaps _within_ a plant. If that was not a valid assumption, the workflow would require tweaking. ![image](https://forum-cdn.knime.com/uploads/default/original/3X/b/3/b333660b4f6f2ac2843b358a8394762b15ab1394.png) Of note to me… I think this was the first time I’d made use of “logical arithmetic” in the math formula node. That node isn’t well known for it’s ability to do conditions, but for basic logic returning 1 or 0, it can conditionally add to the `end time` if the `end time < start time`: [Find Overlaps.knwf](https://forum.knime.com/uploads/short-url/nLeqOwJwi5z6u8GqsEJrTZJieM5.knwf) (26.9 KB) ![image](https://forum-cdn.knime.com/uploads/default/original/3X/a/1/a12b71030394d23e93d033a0775169a4ce542a7a.png) ![image](https://forum-cdn.knime.com/uploads/default/original/3X/3/7/374b9d3611582fe923fadc1ea749cb696baf3ba6.png) ---
**Author:** ![gonhaddock](https://forum-cdn.knime.com/user_avatar/forum.knime.com/gonhaddock/32/35425_2.png) [@gonhaddock](https://forum.knime.com/u/gonhaddock)\ **Post date:** [May 4, 2022, 9:01pm UTC](https://forum.knime.com/t/identify-overlapped-items/42495/5 "2022-05-04T21:01:21Z")
I am taking care of the game right now… Champions I realised that we have to deliver two separate columns for beginning and end overlap intervals. As one item can deliver both. Even thinking about possible repeated starting or ending item interval is possible. I will rewiew it, only if my heart can afford about this. ---
**Author:** ![gonhaddock](https://forum-cdn.knime.com/user_avatar/forum.knime.com/gonhaddock/32/35425_2.png) [@gonhaddock](https://forum.knime.com/u/gonhaddock)\ **Post date:** [May 4, 2022, 10:46pm UTC](https://forum.knime.com/t/identify-overlapped-items/42495/7 "2022-05-04T22:46:15Z")
@LuisMP I’ve just reviewed the challenge delivering the output in two columns. And this covers the challenge request. ![image](https://forum-cdn.knime.com/uploads/default/original/3X/0/e/0e66e0ec49c90ed586c0084dfc8d12e463dc130c.png) But to be honest, if I think about all the causalities coming with real data… what if an item interval is included in other one? what if several item intervals overlap the same time period? I am starting to have doubts about that this is the needed approach, or at least the whole scope of it. Extended comments on this subject will be appreciated. BR ---
**Author:** ![LuisMP](https://forum-cdn.knime.com/letter_avatar_proxy/v4/letter/l/b19c9b/32.png) [@LuisMP](https://forum.knime.com/u/LuisMP)\ **Post date:** [May 5, 2022, 2:16am UTC](https://forum.knime.com/t/identify-overlapped-items/42495/8 "2022-05-05T02:16:56Z")
Yeah… first of all, I would’ve liked some pens. Game was boring up until the 85th minute. Second of all, perhaps I didn’t give the full story and that’s my bad. So, apart from having the start time and finish time columns, I’ve also got start date and finish date on separate columns each one. I’ve checked most of the data and there seems to not be any overlap between dates, like there’s no order starting on a day and finishing the next day. So far, the approach you gave me works fine. Sent the preliminar data for review and feedback, so I’m waiting for that to see if the approach is enough or if it’s missing something. ---
**Author:** ![duristef](https://forum-cdn.knime.com/user_avatar/forum.knime.com/duristef/32/37106_2.png) [@duristef](https://forum.knime.com/u/duristef)\ **Post date:** [May 5, 2022, 5:05pm UTC](https://forum.knime.com/t/identify-overlapped-items/42495/9 "2022-05-05T17:05:48Z")
I found this problem very interesting, so I’ve tried a slightly different approach, which should encompass all the possible cases ![immagine](https://forum-cdn.knime.com/uploads/default/original/3X/0/0/0023016e8b6f4b0db478186d8376b86e133a4d2e.png) This is the table with the start/finish times and the duration in seconds ![immagine](https://forum-cdn.knime.com/uploads/default/original/3X/e/2/e2d752e1f778d7425a378bc3759729175366c4e1.png) This is the (hopefully correct) output table, with the overlapping items. The rightmost two columns contain the overlap in seconds and one of the 4 possible “patterns” of the overlap. ![immagine](https://forum-cdn.knime.com/uploads/default/original/3X/5/7/574a15c7478bffb5e36493dfa4043baef134922f.png) > **[20220505\_overlaps – duristef](https://hub.knime.com/duristef/spaces/Public/latest/solutions/20220505_overlaps~tbeOLuMNvnkPQD-z)** > > Solutions for data science: find workflows, nodes and components, and collaborate in spaces. ---
**Author:** ![system](https://forum-cdn.knime.com/uploads/default/original/1X/ab3ccf34482a0329361734a18199390177204f15.png) [@system](https://forum.knime.com/u/system)\ **Post date:** [August 3, 2022, 5:06pm UTC](https://forum.knime.com/t/identify-overlapped-items/42495/10 "2022-08-03T17:06:15Z")
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.