How to group by ID and find gaps in dates to determine start and end date in Alteryx?

Question

I have a dataset that looks like the following;

ID       Date
1   2018-07-23
1   2018-07-24
1   2018-07-25
1   2018-07-26
1   2019-12-31
2   2020-01-01
2   2020-01-02
2   2020-01-03
2   2020-01-06
2   2020-01-07

What I am trying to achieve is convert this dataset into start and end dates for wherever their are any gaps in the date column.

So the expected output would look like the following;

ID     StartDate        EndDate
 1    23-07-2018      26-07-2018
 1    31-12-2019      31-12-2019
 2    01-01-2020      03-01-2020
 2    06-01-2020      07-01-2020

As you can see, both IDs have two rows because there was a gap in the dates.

Please Help and thank you.

score 0 · Answer 1 · answered Aug 12 '21 at 06:24

For StartDate: use the MultiRowFormula tool. In its options, Group by ID, tell it to default to the value of the nearest row, and use an expression something like:

IIF ([Date] == DateAdd([Row-1:Date],'days',1) THEN
    [Row-1:StartDate]
ELSE
    [Date]
ENDIF

That will create all the [StartDate] for you, but you'll still have multiple rows. So, then do an Aggregation tool: Group by both ID and StartDate, and create EndDate as max([Date]). That should provide the correct EndDate to go with each StartDate.

How to group by ID and find gaps in dates to determine start and end date in Alteryx?

1 Answers1