Needing a way to pull next valve based on date

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.

Test Alteryx.xlsx

Preparation

Accepted answers

aatalai

@blakeah23 does the attached workflow help?

Needing a way to pull next valve based on date.yxmd

All comments

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

Test Alteryx.xlsx

aatalai

@blakeah23 does the attached workflow help?

Needing a way to pull next valve based on date.yxmd

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703