Overlapping of timestamps for value of another column

[Barajas]

Hello Everyone!

I attached a screenshot of my current table for a better comprehension of my current issue, and thank you all in advance for your ideas.

I need to be able to compare and mark those “Items” (Short Description column) that are alike and overlapping (Created and Resolved columns). The manual analysis of this information is easy: I look for those items in the Short Description Column that are exactly the same, then I check when were they created and when they were resolved if an alert of the same Item was created while a previous one was still not resolved, then it is overlapping and that is what I want to pinpoint.

So far, I am able to see how many occurrences of the same Item are happening, but I am not sure how to deal with the timestamps and a second occurrence of the same alert.

Hope this is clear enough and thank you again. This community is awesome.

[FtmhRahimi]

Hi @Barajas
I’m not sure I understand your question. But you can find overlaps using the “Lag”, “Sorter” and “Time Difference” nodes. I created a small workflow for this. You can probably reach your goal by placing a loop on all the same items.
find overlap.knwf (14.1 KB)

[HansS]

Hi @Barajas

@FtmhRahimi

by placing a loop on all the same items

a.k.a. Group Loop
gr. Hans

[Barajas]

@FtmhRahimi
I think the reason why that error shows up has something to do with the inconsistency of the data on the “created” column.

For some reason, KNIME is removing the “00” (zeros) whenever on my original data (excel) the created timestamp ends with no seconds (2021-08-20 14:37:00 is being converted to 2021-08-20 14:37)

I keep investigating but thank you for all your help

[Barajas]

@FtmhRahimi Thank you so much for your input, it has been the backbone of all my work today and although I have come a long way thanks to you, I still have a few minor issues. I am attaching to this reply my current workflow with examples of the data for everyone to understand the specifics of my issue.

While your workflow allows marking an overlapping timestamp based on one condition (the previous timestamp comparison) I need to be able to check for two more conditions (name of the company and a short description - refer to screenshot)

I hope this provides more detail about what I am pursuing. Thank you again.example.knwf (269.2 KB)

[FtmhRahimi]

Hi @Barajas
as I said before, I think if you use “group loop” on the short description column then check overlap for each group, problems will be solved

[Barajas]

I have been checking different examples of how the group loop node can be integrated, but I cant still make it work with what I need. So far, I have been able to export everything to excel into sections (those sections are exactly what I want but with the lag node)