Please help- how to randomly match groups from two data sets?

Hi,

I'm a new user to Alteryx, and here is a tricky case. I've used a simple example to illustrate the problem, please see the attachment. Basically, I need to assign doctors to 20 pets of 6 owners. One pet one doctor. And there are several rules to follow:

1. The assignments of doctors to pets owner should be randomly picked; 2. Every doctor can't choose the owners who they have served before;3. Better to assign one doctor to one owner, so that owner only have to visit as few doctors as possible.

Which function(s) should I use in Alteryx? Hope to get your advices. Thank you very much!

Best,

Grace

Community ask Q.xlsx

Tips and Tricks

Accepted answers

michael_treadwell

Great idea using Tile! I bet if you play around with the tiling method you could get something to work for you without manual intervention. If not, you could always imbed the tile tool in a macro and set the tiles using an algorithm of your choice.

For your second question, I recommend watching this video on Iterative Macros: https://www.youtube.com/watch?v=xExR3-rmcDc

Basically, every owner that isn't matched in the first iteration of the macro flows through the U (unmatched) output. An iterative macro will re-iterate using just the records in the U output and repeat until (1) no records are unmatched or (2) the maximum iteration number is reached.

All comments

michael_treadwell

There are going to be many ways to tackle this problem, each with strengths and weaknesses.

Attached is something to get you started, perhaps. It is an iterative macro creates matches based on a rank of RANDINT() and iterates until all owners are matched with a doctor that hasn't previously served them.

Couple of issues with my process:

1) There is no accounting for balance, especially when the number of patients and doctors grow. So you could end up with a few doctors having many patients.

2) My macro assumes each doctor has only served one previous patient. That is a large assumption but it should be too difficult to fix with a little work.

One note about your post, you say that it is "better to assign one doctor to one owner, so that owner only have to visit as few doctors as possible." Because of that I have only matches owners to doctors, not individual pets to doctors. However, in your sample output you have pets from the same owner being served by different doctors.

match.yxzp

Grace_Qin

Thanks Michael for the advice. I've tried your macro and figured out one solution to the balancing issue (1). By using TILE function, I divide the 'Owners info' into four groups with equal records so that four doctors are likely to have same number of patients. (if the database become larger, it's useful) But the solution is not smart enough as I have to manually set the 'number of tiles' according to doctors list. Once the 'doctors info' updated, I have to customize the tile number manually...

Another question from a starter: After opening your 'match_iterate', I see two outputs marked as 'U' and 'M' which represents 'unassigned owners & pets' and 'no previous served issue' respectively. But in the macro output 'M', all owners have been assigned properly. How to realize that?

Thanks!

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335