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: EmployeeReport KeyTransaction DateCityCountryFYQJob Charge TypeDepartmentAN1162025/01/2020DubaiUAEFY20Qtr3BillableTTAN1162026/01/2020DubaiUAEFY20Qtr3BillableTTAN1162027/01/2020DubaiUAEFY20Qtr3BillableTTAN1162028/01/2020DubaiUAEFY20Qtr3BillableTTAN1162029/01/2020DubaiUAEFY20Qtr3BillableTTAN1162002/02/2020JeddahKSAFY20Qtr3BillableTTAN1162003/02/2020JeddahKSAFY20Qtr3BillableTTAN1162004/02/2020JeddahKSAFY20Qtr3BillableTTAN1162005/02/2020JeddahKSAFY20Qtr3BillableTTAN1162009/02/2020JeddahKSAFY20Qtr3BillableTTAN1162010/02/2020JeddahKSAFY20Qtr3BillableTTAN1162011/02/2020JeddahKSAFY20Qtr3BillableTTAN1162016/02/2020JeddahKSAFY20Qtr3BillableTTAN1162017/02/2020JeddahKSAFY20Qtr3BillableTTAN1162018/02/2020JeddahKSAFY20Qtr3BillableTTAN1162019/02/2020JeddahKSAFY20Qtr3BillableTT Output: FYQJob Charge TypeDepartmentCityCountryReport Legacy KeyCheck inCheck outFY20Qtr3BillableTTDubaiUAE1162025/01/202029/01/2020FY20Qtr3BillableTTJeddahKSA1162002/02/202005/02/2020FY20Qtr3BillableTTJeddahKSA1162009/02/202011/02/2020FY20Qtr3BillableTTJeddahKSA1162016/02/202019/02/2020 Ideal Output: FYQJob Charge TypeDepartmentCityCountryReport Legacy KeyCheck inCheck outFY20Qtr3BillableTTDubaiUAE11620-125/01/202029/01/2020FY20Qtr3BillableTTJeddahKSA11620-202/02/202005/02/2020FY20Qtr3BillableTTJeddahKSA11620-309/02/202011/02/2020FY20Qtr3BillableTTJeddahKSA11620-416/02/202019/02/2020 I tried to do it with the summarize tool but couldn't haha... Would appreciate your help

Grouping rows by consecutive dates with other criteria

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

Designer Cloud

Date Time

Data Investigation

Accepted answers

ShankerV

Hi @celinearamouni

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

Many thanks

Shanker V

All comments

DenisZ

I have provided the full workflow below

GroupNumb.yxmd

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