community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

How to design workflow for identifying Permutation and Combinations

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. 

Please help with workflow.

 

Alteryx Certified Partner

Hi @RAHULMISHRA,

 

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

Quasar

hi @RAHULMISHRA

 

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.

 

Solution.png

 

Dan

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. 

Quasar

Hi @RAHULMISHRA

 

   As per your requirements 

 

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

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
Quasar

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

 

Dan

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