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

[celinearamouni]

Here is a screenshot with the columns of rank and tile. Let me know if you need anything else to be able to tell my mistake.

[ShankerV]

Hi @celinearamouni 

Found little confusing with your screenshots.

Can you please do me a favour.

1. Please share the input first

2. Share the output seen in your alteryx

3. Then share the complete workflow

or Share your Alteryx workflow, I can check and confirm what it is the issue.

The issue might be very small, couldn't get it as screenshots was for partial workflow and couldn't guess the input.

Many thanks
Shanker V

[ShankerV]

@celinearamouni 

Did your issue got resolved?? Derived your expected solution??

Many thanks

Shanker V