Filtering out the correct columns if conditions met

[ashamluo]

Hello I was wondering if anyone would know how to filter out the rows form a data set if conditions are met.

I have a dataset that gives me case statuses of different customers(ID), they may be open, reopen, resolved.

I want to know how much time it took a case from open/reopened to resolve. But i just want the most recent open/reopened and resolve cases.

So i did a group by ID, group by status, then taking the MAX. 

ORIGINAL DATASET

ID	status	Timestamp
AB-001	open	12/01/22 13:00:00
AB-001	resolved	12/02/22 11:00:00
AB-001	reopened	12/02/22 13:00:00
AB-001	resolved	12/02/22 18:00:00
AB-002	open	12/05/22 07:00:00
AB-002	resolved	12/06/22 13:00:00
AB-003	open	12/06/22 14:35:00
AB-003	resolved	12/06/22 07:49:00
AB-003	reopened	12/07/22 07:00:02
AB-003	resolved	12/12/22 10:00:00
AB-004	reopened	12/05/22 11:52:43
AB-004	resolved	12/06/22 13:02:59

Then after taking the MAX the data set would look like this which would filter out the other resolve cases leaving only the most recent resolved cases.

ID	status	Timestamp
AB-001	open	12/01/22 13:00:00
AB-001	reopened	12/02/22 13:00:00
AB-001	resolved	12/02/22 18:00:00
AB-002	open	12/05/22 07:00:00
AB-002	resolved	12/06/22 13:00:00
AB-003	open	12/06/22 14:35:00
AB-003	reopened	12/07/22 07:00:02
AB-003	resolved	12/12/22 10:00:00
AB-004	reopened	12/05/22 11:52:43
AB-004	resolved	12/06/22 13:02:59

After this I am not sure how to proceed. I want to take the date difference of the most recent open/resolved or reopened/resolved per ID.

But there are many cases and I am not sure how to do it.

1. If open and reopened are both present then I am taking reopened.

2. If open is present and reopened is missing then I am taking open.

3. If reopened is present and open is missing then I am taking open. (weird cases)

This is my end goal.

ID	Status	Timestamp
AB-001	reopened	12/02/22 13:00:00
AB-001	resolved	12/02/22 18:00:00
AB-002	open	12/05/22 07:00:00
AB-002	resolved	12/06/22 13:00:00
AB-003	reopened	12/07/22 07:00:02
AB-003	resolved	12/12/22 10:00:00
AB-004	reopened	12/05/22 11:52:43
AB-004	resolved	12/06/22 13:02:59

Thank you in advance!

[Matthew · Accepted solution]

this might do the trick

filter the resolved timestamp out, summarise the max timestamp by ID for both the "true" and "false" outputs from the filter, then join them back together.. from there you can calculate the difference between the two timestamps

[IraWatt · Accepted solution]

Hey @ashamluo,

I think you want to cross tab your data to apply these conditions. I had a try but I'm not sure I follow your logic:

The formula has your ruleset to take the value you want but it does not seem to match your desired output

[binuacs · Accepted solution]

@ashamluo one way of dong this with the sample tool

[ddiesel]

Hi @ashamluo!

Will you let us know if any of these solutions worked for you?

Thanks,
Deb

[ashamluo]

Thank you for all the solutions!  I am sorry, previously I lost my internet while accepting the solution.

[IraWatt]

No worries @ashamluo ! Glad you got it sorted 😄