Need to find the TRIP for multiple entries

[Harish_G]

Hi,

I Need to find the TRIP  for multiple travels happened between dates.

Attached is the sample data and I need to auto generate TRIP ID for the entries.

[DavidSta]

Hi @Harish_G,

I am not able to see any rule based on the dataset you provided to generate the TRIPID.

I have no idea why row 7 should have the same ID like row 8.

And the way around why row 10 has not the same ID like row 11.

You need to give us some more insights how to interpret this dataset.

Maybe describe your thinking process how the TRIPID is manually generated.

Best regards,

David

[Harish_G]

HI @DavidSta ,

sorry for the confusion. I revised the data and attached the sample.

I have input and expected output in the attached format.

I'm looking to find the start and end time for a trip and Departure and Arrival country.

Regards,

Harish

[DavidSta · Accepted solution]

Hi @Harish_G,

you can create this expected output with a single Summarize Tool.

Please see attached workflow as reference.

Your unique identificator is the ReportID, EmployerID and Itinerary Key. So this is going to be your "Group By"

The Dataset should be sorted by these columns and the StartDate before starting with the Summarize Tool.

Now you can use the Min/Max functions for your trip dates and the First/Last for the countries to show in your report. To get the right First/Last results it's required to be sorted.

Best regards,

David

[Harish_G]

Thanks @DavidSta . 

[Harish_G]

Hi @DavidSta 

can you please advise how to handle this scenario?

I want to know the country he was staying for that day. 

Regards,

harish

[DavidSta]

Hi @Harish_G,

next time please create a new thread as this one was already marked as solved. Then you will get as well more support from other community members.

With this you help to keep the community clean and searchable, to help others with similar request to find their required solution.

Besides this let me give you a hint what to look for.

You need to create a row for every day which is not present in your data source. This is done with the "Generate Rows" tool, generating a row for every day in your data set. Then this can be joined with your original data set and then with Multi-Row and Multi-Field Formula cleaned up.

This solution might require a few tweaks as there are e.g. no values with travel across multiple days But you should get the idea with this sample.

Best regards,

David