Needing a way to pull next valve based on date

[blakeah23]

I am having a problem I need some help with, I have some data where assignments are being made with a start date and an end date.

For example, John Smith was assigned to ABC client from 01/01/2023 - 05/01/2023 and then Adam Paul was assigned to the same ABC client from 05/01/2023 - 08/01/2023. My data has these two assignments on separate rows, I am needing a way to pull the person assigned after John Smith for all clients. All clients have multiple assignments before and after.

I have attached an example of my data; in theory I need to pull the person assigned after John Smith specifically for all the clients on my list.

[Matt_D]

Hi @blakeah23  I've been away from the forum for ages and I forgot what it's like having to solve a cryptic riddle before even trying to solve anyone's data problem 🤣

Can you give me an example of how the final output should look?

Your data suggests Seth Avery was assigned after John Smith.

[blakeah23]

Correct, I may have set up my example wrong so I will re-attach it. I would want a way to pull Adam Paul in since he is who was assigned directly after John Smith. So I want to pull this logic for John Smith across all clients, does not matter if its Adam Paul as it could be anyone. 

So for final output - would want client ABC to pull Adam Paul

[aatalai · Accepted solution]

@blakeah23 does the attached workflow help?

[blakeah23]

That gets me really close, the only issue is I have about 30,000 clients I need to do this for and all could have the same assignments. So needing the "who's next" for each client

[aatalai]

ok is there  a spfecic date you will filtering on?

[blakeah23]

Ideally the person with the next start date.

So John Smith start date 1/1/2023 and Adam Paul start date 2/1/2023 and Jenny Greg 3/1/2023 I would want to pull Adam Paul for that client, and the same for all clients

[aatalai]

Ok then look at the output before the filter (the multirow out put) does that get what you are looking for?

[blakeah23]

It looks like its working but the sort by date is not being respected on the next steps so its not pulling the "next" person correctly.

[blakeah23]

I think the issue is it needs to be sorted by both client and by date started, not just date started. We are looking for the "next" person based on the client