grouping rows for consecutive dates (with two criterias)
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello All,
I'm new in Alteryx and until now most of my work I made using Excel and VBA - so please excuse me if my case is insolvable in ALTX - I simply do not know limitation of the program.
Right now I'm trying to learn this new tool and move some (preferably most) of the processes related to data preparation to ALTX.
In this case I need to merge/group rows for consecutive Trips into one, keeping information about earliest start date and latest end date for the destination.
Rows are to be grouped ONLY for same TRIP ID and Country.
Continuous trip means both:
1) start date 1 : 1/1/2017 end date 1: 1/2/2017; start date 2: 1/2/2017 end date 2: 1/4/2017
2) start date 1: 1/1/2017 end date 1: 1/2/2017; start date 2: 1/3/2017
So for Trip ID 321, I need to check if "Country" is repeating and if the trip is continuous and merge it into 1 row, for 123 there is no continuity:
TRIP ID | Country | start date | end date | number of days in travel | TRIP ID | Country | start date | end date | number of days in travel | ||||||
123 | Poland | 1/1/2017 | 1/3/2017 | 123 | Poland | 1/1/2017 | 1/3/2017 | ||||||||
123 | Germany | 1/3/2017 | 1/5/2017 | 123 | Germany | 1/3/2017 | 1/5/2017 | ||||||||
123 | Poland | 1/5/2017 | 1/7/2017 | 123 | Poland | 1/5/2017 | 1/7/2017 | ||||||||
321 | Germany | 1/2/2017 | 1/4/2017 | 321 | Germany | 1/2/2017 | 1/6/2017 | ||||||||
321 | Germany | 1/4/2017 | 1/6/2017 | 111 | Poland | 1/10/2017 | 1/12/2017 | ||||||||
111 | Poland | 1/10/2017 | 1/12/2017 | 222 | Poland | 1/14/2017 | 1/27/2017 | ||||||||
222 | Poland | 1/14/2017 | 1/20/2017 | ||||||||||||
222 | Poland | 1/21/2017 | 1/23/2017 | ||||||||||||
222 | Poland | 1/24/2017 | 1/27/2017 |
I'm attaching file with the example (presented in table above), and sample data.
I've solved it using VBA loops and tabular formulas but I have not idea how to solve it in ALTX.
Thank you for help
Have a great weekend.
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Grovnic,
Welcome to Alteryx!
There is a tool in the preparation menu called "Multi-Row Formula" which is perfect for this. This tool lets you define how many rows you wish to look at relative to the current row, you can then also use the group by option to make it so your grouping on TripID & Country. You can then simply drag the field you want from the next collumn into the current one. I am not sure 100% how much information you would want to pull in but its not to bad to figure it out.
Cheers
Chris
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Grovnic
Please find attached an example workflow to achieve your results.
I believe is a fairly neat solution but it is a Friday afternoon, so you may have to excuse it if there is a neater one.
I have used the multi-row formula to do the main bulk of the logic. Just included a couple other parts to clean up your data.
Then at the end I can use the summarize tool to combine the consecutive dates.
Thanks
Joe
Edit: looks like I was just beaten to the solution above, but at least we all agreed to do it a similar way :-)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@patrick_digan - thank you for this - I'll just try to apply this to my workflow and original set of data and further work to understand the logic used in this "multi row formula".
Will let you know how it works!
Regards
G.
