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.
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.
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