This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I have huge database from our Travel team which show Company ID of person, Name of Person, Travel Start Date, Destination City, Amount of Air Ticket, ticket Number etc. for complete 2018. I want to prepare a excel output which can show unique combination of people who are travelling together to same destination city on same travel Start date. How can i build one with use of people's unique company id?
I need this list of people descending order.
e.g. If A-B-C ( all 3 person) traveled to Atalanta whopping 85 times in 2018. It should appear A-B-C in one column Destination City in another column and Count of trips ( Count of ticket Numbers) as 85 in third column. If A-C-E-F-G ( 5 People) traveled together to New York 130 times it should appear as A-C-E-F-G New York 130. Also, A-C-E-F-G and C-E-G-F-A are same. hence, only unique combinations should be reflected in results.
Here a simple solution to your problem. Sort by Date, City and person. Create the travel groups by concatenating the people. Having them sorted in the previous step handles the permutations of the same people. Then count the trips.
Thank you for working out. However, this is not complete solution.It does not consider all combinations. I think we need to have one more intermediate process to reflect all possible combinations. for example, in your record 1,2,3 and 4, how can I form 5 different rows in travelling party such as A-B, A-C,A-D,B-C,B-D,C-D,A-B-C,A-B-D,A-C-D,B-C-D, A-B-C-D, A, B, C,D i.e. 15 combinations.
Mathematical solution would be 4C1+ 4C2+ 4C3+ 4C4.= 4+6+4+1=15 Combinations.
"...can show unique combination of people who are travelling together to same destination city on same travel Start date"
The solution I posted gives you all the possible combinations in your actual data. If the combination doesn't occur in the output, it's because it's not in the data and has never occurred, i.e. A-C flying to Atlanta on the same day has never occurred. It is possible to generate a list of all possible combinations of People groups , Dates and places, but then you would have a very long list with mostly 0 occurrences
In My Data, there are many people who are travelling. In my last excel sheet, I have considered 15 combinations coming out of A,B,C and D by using mathematical Combination formula ( 4C1+ 4C2+4C3+4C4). Similarly, in my actual data set, There are some place where 45 people are travelling same day to same destination. I just thought to tell you that i want to Implement mathematical Combination formula in Alteryx. Thanks in Advance. Hope my last excel explains everything.