Finding date gaps in a list of start and end dates

[LB_Knime]

I’m new to Knime. I’m sure it can do what I want, but I don’t know the tools/techniques that will get me there. Any tips are appreciated…

I have a google sheet with 4 columns (ID, Org, Start date, End date).
There can be multiple rows per ID - each row is the start and end of a member’s participation in an Org. A member can change Orgs more than once and sometimes be a part of two Orgs. (And there may be blank rows, which I could remove beforehand if necessary, but would be happy to handle in my workflow.) Sample data is below.

What I’d like to do is read through the rows and check per member (ID):

• Are there any gaps in participation? (End date does not match the following Start date for the same ID)
• Which members (IDs) are in more than one Org? (One ID has more than one Org without end dates.)

Basically, I need a method for reading through the list and looking at the date values within the same ID group and distinguishing among the Orgs within that group as well. Should I transpose? Should I do a self-join? A loop? Some other clever trick?

|ID|Org|Start date|End date|
|62|A1|2021-11-23|2021-12-10|
|62|A2|2021-12-10||
|||||
|18|A1|2021-11-23|2021-12-06|
|18|C1|2021-12-16||
|40|A1|2021-11-23|2021-12-06|
|||||
|40|C1|2021-12-06||
|||||
|77|A1|2021-11-23|2021-12-06|
|77|C1|2021-12-06||
|44|A1|2021-11-23|2022-02-09|
|44|D1|2022-02-09|2022-02-09|
|25|J1|2020-04-16|2021-04-28|
|25|J2|2021-05-02|2021-10-19|
|25|J1|2021-10-19||

Thank you!

[bruno29a]

Hi @LB_Knime , it can surely be done, and in a few ways.

Just to confirm I understood the definition of date gap for the same ID, if the next start date after an end date is on the same day, then there’s no gap. However, if it’s on the next day or any other future date, it’s a gap, is that correct? There’s no gap only if the next start date is the same as the previous end date, correct? That’s what I understood by “End date does not match the following Start date for the same ID”

EDIT: I’m making the assumption that there is no gap only if start date is the same as previous end date of the ID.

I put something together that looks like this:

I created an Excel file with your data with empty rows/columns to simulate the Google Sheets, which I included with the workflow. The file looks like this:

I’ve not used Google Sheets with Knime too much, but the Excel Reader has options to ignore hidden/empty rows/columns, so after reading the Excel file, this is what I get in Knime:

Here’s the result for checking for gaps:

Here’s the result for check which member (ID) is in more than 1 Org:

And here’s the workflow:
Find date gaps between start and end dates.knwf (33.9 KB)

[LB_Knime]

Thanks @bruno29a ! We’re off and running. I’ll need to download your work and dig in, but will definitely come back here once I’ve done that.