Group by Name and Date Within 10-Day Window

HakimRazalieq1

Hi Community,

I’m working on a workflow where I need to group rows based on two conditions:

- Group by `[Name]`
- Within each name, group rows where the date difference between records is **10 days or less**

If the gap between rows is **more than 10 days**, a **new group ID** should be assigned.

Here’s a simplified example of my data:

Screenshot 2025-07-01 at 4.19.26 PM.png
My expected output are as below:

Screenshot 2025-07-01 at 4.19.35 PM.png
Thanks in advance for any help or suggestions!

Workflow

Best Practices

Accepted answers

All comments

davidskaife

Hi @HakimRazalieq1

Here is a way of doing it using the Multi-Row Tool:

First convert your dates into the standard format for Alteryx, then create a 'Grouping' column with a default value of 1, then using the Multi-row tool update the Grouping column with the below formula

IF (DateTimeDiff([DateTime_Out],[Row-1:DateTime_Out],"days") <= 10) AND ([Name] = [Row-1:Name])
THEN [Row-1:Grouping]
ELSE [Row-1:Grouping]+1
ENDIF

Screenshot 2025-07-01 100239.png

Group By.yxmd

KGT

The way that @davidskaife has done it, is the way that I would as well. Nice, clean and simple! It's the way that I would tell people to do it if running a training session as well.

I will note though:

Setting the field Grouping=1 is not required, but helps explain what's happening.
A sort may be needed.
Where David has included [Name] = [Row-1:Name], is instead of selecting [Name] as a grouping field. For this type of problem, I would do the same as David. The difference is that if you selected the grouping field, then your data will be out of order on the output as the processing will happen on different cores for each group (theoretically).

HakimRazalieq1

Hi @davidskaife ,

Thanks for your help with my earlier issue. I’ve tested your workflow using my own dataset, but it seems the method isn’t working as expected on this new data.

Specifically, for Ali’s data:
Group #4 should only include dates up to 2021-01-11, which is 10 days from Ali's first date (2021-01-01). However, the workflow is currently including 2021-01-13 in the same group, which is incorrect.

The correct behavior should be:

Group #4 includes dates from 2021-01-01 to 2021-01-11.

Group #5 should then begin with 2021-01-13, continuing to include any dates within the next 10 days from that point.

Appreciate your help in looking into this again!

Group By - HR.yxmd

HakimRazalieq1

Hi @davidskaife

Yes, that’s correct. The logic should group records based on whether each date is within 10 days of the first date in the group, not just 10 days from the previous record.

davidskaife

Hi @HakimRazalieq1

Here you go, one approach to it! I've built a small Iterative Macro that cycles through the data and assigns the 'First_DateTime_Out' for that named group. i.e all Ali are assigned to 2021-01-01 as they are within 10 days, but not 2021-01-13 or 2021-01-24 - these records iterate through and get checked again, and repeat!

Screenshot 2025-07-10 184327.png

I've tidied up the main workflow a bit as well, removed the Grouping = 1 Formula tool (this is handled by the Multi-row tool now, where Record ID = 1), and updated the Grouping Formula so it checks both the Names and the DateTime_Out rows match.

IF [RecordID] = 1
THEN 1
ELSEIF [Name] = [Row-1:Name] AND [First_DateTime_Out] = [Row-1:First_DateTime_Out]
THEN [Row-1:Grouping]
ELSE [Row-1:Grouping]+1
ENDIF

You end up with this grouping, which should be what you're looking for?

Screenshot 2025-07-10 184646.png

Group By - HR.yxzp

J-Li

I'd create a workflow in the general lines of:

Ingest File => Summarize Min Date per User => Join with Original Date Set => Filter Out Records Older than Min Date + 10 => Select Columns per Desired Layout

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703