Lengths of time between rows in a table that can be more than one row apart

[hhaw]

Hi,

I have a problem that I am finding quite difficult and I am still relatively new to KNIME. I am trying to calculate the closest important day to every other day in a group (country in this example. Each country can have one or many important days (or none). I am finding comparison between rows that are not immediately above / below each other to be complex.

In my example: for AUS, there is one important day (20/01/2021), the “Closest important day” column needs to be generated, expressing the (absolute value) number of days each regular day is different from that important day. GER has no important day, so the “Closest important day” column remains “?”. USA has two important days, which means each regular day that falls under this country must have its “Closest important day” column calculated with respect to the closest one to it.

Thanks for any help, I appreciate it a lot!

[aworker]

Hi @hhaw

May be this other related post could help you to find a solution to your question:

Joining two tables based on date ranges

Hi @ViTLe My pleasure to help Joining tables by -date ranges- is not something trivial at a first glance so I understand the difficulty to get to a solution Please find below a possible solution. The -Sorter- node is not stricty needed appart from easing the understanding of the results. The Chunk Loop is neither strictly needed but it will help you to optimize the use of your computer memory if you have huge tables as you said. Otherwise, it could be done without it. …

In the above post, a workflow shows how to convert the dates into absolute integer numbers which allows to easily compare dates and know which ones are the closest.

Hope this helps.

Best

Ael

[takbb]

Hi @hhaw, depending on how much data (and memory) you have, you can also achieve this without a loop by joining all rows to all other rows for a given country, and then performing the required date comparisons.

The attached workflow gives an example of how this might work

Find Closest Date.knwf (34.2 KB)

[hhaw]

Thanks @takbb, this works wonderfully!

[Thyme]

Hi Brian,
sorry to desecrate your workflow, but I had to Node Golf this. I had an idea that could reduce the number of nodes by one (1):
Using a Row Splitter before the first Joiner get’s rid of the “Grouped Cross Join” and renders the 2 Row Filters afterwards obsolete.

Could be relevant for hhaw since this eats less RAM.

Find_Closest_Date_tuned.knwf (114.2 KB)

[takbb]

Hi @Thyme, please don’t apologise for being constructive, but thanks for the thoughts. Great idea. That’s what this forum is about… building on each other’s ideas.

I often come back to something later on and think “why didn’t I just…”

[Thyme]

Yes, standing on the shoulders of giants. Or a big enough pile of dwarfs, works either way.

It happened in the past that I unintentionally hurt people because I didn’t pay enough attention to how I come across. No big deal on my side, just trying to make sure it doesn’t happen again. But somethymes I overshot the target