Grouping dates by certain conditions

Hi there,

I’ve been having trouble grouping dates across two different columns. The challenge is that the Email, Service, Country, and Purpose fields must match for the dates in the Start and End date columns to be grouped. However, if there is even a single day separating the dates in the Start and End columns (e.g., if all required conditions are met, but the end date is 2024-11-12 and the start date is 2024-11-13), they should not be grouped. Data report is large, with huge variety of emails.

Please let me know if this isn’t clear, and I’d be happy to provide further details.

How it is:

How it should be:


Example.xlsx (12.6 KB)

Huge thanks!

I’ve been using Lag column functions for all these columns and somewhere between Rule engine, Moving Aggregator and Group By I lost the logic.

Please help :smiling_face_with_tear:

It’s not really clear to me what you’re trying to do.

What’s the difference between the 2 screenshots? Where/what are the groups?

@elsamuel Hi! Sorry, it is really confusing. Dates should be grouped, if conditions as email, service, country and purpose are the same.
In my very first message, in the second screenshot these green lines are made in one line with mentioned details in this sc comments

Hi @businesssun

This needed a little bit “sorting and lagging” because it was not straight forward to identify the records to be grouped. See if this works for you. grouping_dates.knwf (62.0 KB)

gr. Hans

3 Likes

Hi @HansS , this works very well. I just needed to sort out the data before using this workflow and it worked. Amazing, thank you a lot! :pray:

Also, could you explain a bit how did you come up with that solution and logic? Also could you explain how did you make node connection between Rule Engine and Sorter that straight and pointy? Looks very neat :see_no_evil:

Hi @businesssun

Glad this solution works for you. Unfortunately I can’t explain how I came up with the solution. This solution in particular involved some trial and error, back and forth. It must have something to do with experience.

Happy KNIME-ing

1 Like

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