grouping rows for consecutive dates (with two criterias)

[Grovnic]

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.

[clant]

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

[patrick_digan · Accepted solution]

@Grovnic I would use the multi-row tool like @clant suggested to add a grouping field. Then you can use the summarize tool. I've attached a quick sample to hopefully point you in the right direction!

[JoeS]

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 :-)

[Grovnic]

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