Grouping rows by consecutive dates with other criteria

[celinearamouni]

Hi everyone,

I've looked around the community for an Alteryx solution but can't seem to find one - so posting it! 

Basically what I have is a row for each night at an hotel by the employee. For the same report # submitted by that employee, he stayed in multiple cities and multiple times. I've color coded each stay together based off the consecutiveness of the dates. So what I'm looking to do and group all of the nights for each stay together with my output having the check in (the earliest date) and the checkout (the latest date). 

Input:

Employee	Report Key	Transaction Date	City	Country	FY	Q	Job Charge Type	Department
AN	11620	25/01/2020	Dubai	UAE	FY20	Qtr3	Billable	TT
AN	11620	26/01/2020	Dubai	UAE	FY20	Qtr3	Billable	TT
AN	11620	27/01/2020	Dubai	UAE	FY20	Qtr3	Billable	TT
AN	11620	28/01/2020	Dubai	UAE	FY20	Qtr3	Billable	TT
AN	11620	29/01/2020	Dubai	UAE	FY20	Qtr3	Billable	TT
AN	11620	02/02/2020	Jeddah	KSA	FY20	Qtr3	Billable	TT
AN	11620	03/02/2020	Jeddah	KSA	FY20	Qtr3	Billable	TT
AN	11620	04/02/2020	Jeddah	KSA	FY20	Qtr3	Billable	TT
AN	11620	05/02/2020	Jeddah	KSA	FY20	Qtr3	Billable	TT
AN	11620	09/02/2020	Jeddah	KSA	FY20	Qtr3	Billable	TT
AN	11620	10/02/2020	Jeddah	KSA	FY20	Qtr3	Billable	TT
AN	11620	11/02/2020	Jeddah	KSA	FY20	Qtr3	Billable	TT
AN	11620	16/02/2020	Jeddah	KSA	FY20	Qtr3	Billable	TT
AN	11620	17/02/2020	Jeddah	KSA	FY20	Qtr3	Billable	TT
AN	11620	18/02/2020	Jeddah	KSA	FY20	Qtr3	Billable	TT
AN	11620	19/02/2020	Jeddah	KSA	FY20	Qtr3	Billable	TT

Output:

FY	Q	Job Charge Type	Department	City	Country	Report Legacy Key	Check in	Check out
FY20	Qtr3	Billable	TT	Dubai	UAE	11620	25/01/2020	29/01/2020
FY20	Qtr3	Billable	TT	Jeddah	KSA	11620	02/02/2020	05/02/2020
FY20	Qtr3	Billable	TT	Jeddah	KSA	11620	09/02/2020	11/02/2020
FY20	Qtr3	Billable	TT	Jeddah	KSA	11620	16/02/2020	19/02/2020

Ideal Output:

FY	Q	Job Charge Type	Department	City	Country	Report Legacy Key	Check in	Check out
FY20	Qtr3	Billable	TT	Dubai	UAE	11620-1	25/01/2020	29/01/2020
FY20	Qtr3	Billable	TT	Jeddah	KSA	11620-2	02/02/2020	05/02/2020
FY20	Qtr3	Billable	TT	Jeddah	KSA	11620-3	09/02/2020	11/02/2020
FY20	Qtr3	Billable	TT	Jeddah	KSA	11620-4	16/02/2020	19/02/2020

I tried to do it with the summarize tool but couldn't haha...

Would appreciate your help

[DenisZ]

I have provided the full workflow below

[celinearamouni]

Hi Denis,

Unfortunately it didn't work when I used it on a larger number of rows. To make things easier, I've attached my whole dataset. You can check for report # 11620 as a sample that contains the hardest criteria. So if that report is good, all of it should be good

Thanks

[ShankerV · Accepted solution]

Hi @celinearamouni 

Please find the below solution which will work for even very huge dataset.

Many thanks

Shanker V

[celinearamouni]

Could  please attach the workflow please? Thanks!

[DenisZ]

Could you explain what did not work. If there is a grouping that is not wanted, do remove it. If you could attach an ideal output for 11620 then I can look closer at it. 

[ShankerV]

Hi @celinearamouni 

Please find the output for the test2 file attached with raw data.

I did few changes to reach the output as we need to sort the dates.

Many thanks

Shanker V

[ShankerV]

@celinearamouni 

This is your input shared in the Raw file

The heading are modified as per your input.

I modified the Department from T to TT like you shared initially.

Output: 

Many thanks

Shanker V

Workflow:

Step 1: Input

Step 2: 

Step 3: 

Step 4: 

Step 5: 

[ShankerV]

@celinearamouni 

Step 6:

Step 7:

Step 8: 

Step 9:

Step 10;

Here you get the result:

Kindly accept this solution if it provided a solution to your question.

Many thanks

Shanker V

[celinearamouni]

I didn't get the same result as you even though I have followed the same steps. Any idea why?

See below screenshot for report 11620 and workflow.