Grouping and Assigning an ID

[Deano478]

Hi guys,

I've currently hit a road block with a task I'm trying to accomplish essentially what I am trying to do is assign an ID to each person every time they take leave. I have a feeling it has something to do with a multi row formula but all my attempts at configuring it so far have not gone well.

You can see in the proposed output that when the days are consecutive the ID stays the same i.e for Dean you can see 1 appears four times as they have 4 days off in a row. However for Jack you can see he only has one day off for 15/06/2023 so it assigns 3 and moves on to the next perosn.

I hope this makes sense as I'm having trouble even trying to word it 😂 any help is appreciated but I understand if this is also a big ask of anyone

Input:

ID	Name	Date Off
123	Dean	05/05/2023
123	Dean	06/05/2023
123	Dean	07/05/2023
123	Dean	08/05/2023
123	Dean	15/05/2023
123	Dean	16/05/2023
567	Jack	18/05/2023
567	Jack	19/05/2023
567	Jack	20/05/2023
567	Jack	21/05/2023
567	Jack	22/05/2023
567	Jack	23/05/2023
567	Jack	24/05/2023
567	Jack	15/06/2023
123	Dean	20/06/2023
123	Dean	21/06/2023
123	Dean	22/06/2023
123	Dean	23/06/2023
123	Dean	24/06/2023
123	Dean	25/06/2023
123	Dean	26/06/2023

The output I'm trying to accomplish is something like this:

ID	Name	Date Off	Group
123	Dean	05/05/2023	1
123	Dean	06/05/2023	1
123	Dean	07/05/2023	1
123	Dean	08/05/2023	1
123	Dean	15/05/2023	2
123	Dean	16/05/2023	2
567	Jack	18/05/2023	3
567	Jack	19/05/2023	3
567	Jack	20/05/2023	3
567	Jack	21/05/2023	3
567	Jack	22/05/2023	3
567	Jack	23/05/2023	3
567	Jack	24/05/2023	3
567	Jack	15/06/2023	4
123	Dean	20/06/2023	5
123	Dean	21/06/2023	5
123	Dean	22/06/2023	5
123	Dean	23/06/2023	5
123	Dean	24/06/2023	5
123	Dean	25/06/2023	5
123	Dean	26/06/2023	5

[FinnCharlton · Accepted solution]

Hi @Deano478 , here's the configuration you need:

Hope this helps!

[ShankerV · Accepted solution]

Hi @Deano478 

One way of doing this.

Many thanks

Shanker V

[ShankerV]

Hi @Deano478 

Step 1: Input 

Step 2: Change the date alteryx ISO format

Step 3:

datetimeadd([Row-1:Modified date],1,"day")

Step 4:

IF [Modified date]=[Temp date]
THEN [Row-1:Group]
ELSE [Row-1:Group]+1
ENDIF

Many thanks

Shanker V

[Deano478]

@FinnCharlton and @ShankerV You guys are absolute lifesavers this was stressing me out more then it needed to😂 I have sorted my issue now with you guy's help

[binuacs · Accepted solution]

@Deano478 One tool approach