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
Solved! Go to Solution.
Please find the below solution which will work for even very huge dataset.
Many thanks
Shanker V
Could please attach the workflow please? Thanks!
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.
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
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:
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
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.