Date time within time range

Hi,

I have data (Date_time) about some event (Description). Based on some conditions some events are important to me (column “Important” contains “Yes”).

|Date_time|Description|Important|
|07.08.2021 11:53:10|Event 1||
|07.08.2021 12:14:12|Event 2||
|07.08.2021 12:22:31|Event 3|Yes|
|07.08.2021 12:30:09|Event 4||
|07.08.2021 12:51:55|Event 5||
|07.08.2021 13:47:02|Event 6||
|07.08.2021 14:45:00|Event 7||
|07.08.2021 14:48:41|Event 8||
|07.08.2021 14:49:28|Event 9||
|07.08.2021 14:49:33|Event 10||
|07.08.2021 14:50:41|Event 11||
|07.08.2021 14:50:54|Event 12|Yes|
|07.08.2021 14:53:55|Event 13||
|07.08.2021 14:57:55|Event 14||
|07.08.2021 14:58:25|Event 15||
|07.08.2021 14:58:56|Event 16||
|07.08.2021 14:59:30|Event 17||
|07.08.2021 14:59:41|Event 18||
|07.08.2021 14:59:47|Event 19||

Now I want to know which events happend in the time range of 45 minutes after the important one (that with “Yes”)

I got stuck on this and can’t see the solution :slight_smile: . Can anyone help me please with an idea how to make the next step …

Thank you

Hi @andrejz ,

One approach to this is to calculate the time 45 minutes after each event, and then keep only those marked “important=yes”. After that a Missing Value node can be used to “Fill down” the “important event end time”, and you can then compare the time of each event with the last “important event end time” using a Rule Engine.

See attached.

(I’ve assumed your dates are dd.mm.yyyy format, but if they are mm.dd.yyyy format you’ll need to change the format mask in the String to Date&Time node)

image

image

Compare with previous event.knwf (17.1 KB)

5 Likes

I was thinking how to use Loops, but your solution is great.

Thank you very much.

Regards

3 Likes

Hi @andrejz, glad it works for you.

Just as an additional option, I don’t know if you have python installed, and know SQL… but one of the common issues with general joining in KNIME is “range comparisons”. This can be overcome using SQL, and I put together a component a while back to assist in such situations.

In the attached workflow, I’ve added an example of my PandaSQL Join component. So here, it takes the dataset and performs a sql join between “all rows” and just the important event rows.

It provides some additional flexibility if all else fails! :wink:

Compare with previous event-python-pandasql alternative.knwf (28.9 KB)

The PandaSQL Join component is available here, but does need Python 3 installed.

3 Likes

Hi,

Thank you for this second solutrion.
I will reamin on the first one.

Regards

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.