cancel
Showing results for
Did you mean:

Alteryx designer Discussions

How to design workflow for identifying Permutation and Combinations

Meteoroid

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.

Alteryx Certified Partner

Hi @RAHULMISHRA,

Can you post up an (anonymised) sample of the data?

Nebula

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.

Dan

Meteoroid

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.

Nebula

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

Dan

Meteoroid

Dan,

Please tell me how to convert Column A,B,C into Column J,K,L in Alteryx. if this completes, My solution is complete.

Highlighted
Nebula

Now I understand your requirement.  It will take me a while to come up with a solution though.

Dan

Meteoroid

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.

Labels