Filter data based on multiple rows

[richard_gardner]

I'm struggling to figure this one out. I need to be able to figure out the open and close for each case, but include scenarios when a case is re-opened, so it's not as simple as the first open and the last close. I've tried the tile tool but can't seem to get it set right.

My input looks like this:

Case Number	Status	Rep	Supervisor	Status Date	Closure Reason
1111	Open	Mike	Eric	3/17/2021 15:57
1111	Closed	Mike	Eric	3/22/2021 11:20	Resolved
1112	Open	Bob B	Phil	5/29/2021 17:12
1112	Open	Bob B	Phil	5/29/2021 18:00
1112	Open	Bob B	Phil	5/29/2021 18:01
1112	Open	Bob B	Phil	5/30/2021 9:47
1112	Closed	Bob B	Phil	5/30/2021 9:50	Withdrawn
1112	Open	Bob B	Phil	11/26/2021 18:20
1112	Open	Bob B	Phil	11/27/2021 7:14
1112	Closed	Bob B	Phil	11/27/2021 7:44	Resolved
1112	Open	Bob S	Eric	12/17/2021 10:13
1112	Open	Bob S	Eric	12/17/2021 10:24
1112	Closed	Bob S	Eric	12/17/2021 11:06	Resolved
1113	Open	Joe	Phil	5/9/2021 9:30
1113	Closed	Joe	Phil	5/10/2021 10:10	Withdrawn
1113	Open	Sue	Dave	6/7/2021 13:03
1113	Closed	Sue	Dave	6/13/2021 8:38	Resolved

And my output should look like this:

Case Number	Open Date	Close Date	Rep	Supervisor	Closure Reason
1111	3/17/2021 15:57	3/22/2021 11:20	Mike	Eric	Resolved
1112	5/29/2021 17:12	5/30/2021 9:50	Bob B	Phil	Withdrawn
1112	11/26/2021 18:20	11/27/2021 7:44	Bob B	Phil	Resolved
1112	12/17/2021 10:13	12/17/2021 11:06	Bob S	Eric	Resolved
1113	5/9/2021 9:30	5/10/2021 10:10	Joe	Phil	Withdrawn
1113	6/7/2021 13:03	6/13/2021 8:38	Sue	Dave	Resolved

How can I do this?

[Luke_C]

Hi @richard_gardner 

Here's how I would approach this:

1. Parse the date into date time format & sort the data by case/date (just in case data is not already sorted)
2. Multi-row formula to determine if a case has been reopened - this checks a given case number's record to see if the previous record was 'closed'.
3. Separate the closed records and join them back to the open records to get the open/close time in the same row
4. Sort data again
5. Take the first instance of each Case/Close time to remove records with repeated open statuses

[ArtApa]

Hi @richard_gardner - Almost everything in Alteryx can be done in a couple of different ways:

[atcodedog05]

Hi @richard_gardner 

Here is my take on it

Workflow:

Hope this helps : )