Conducting A Weighted Random Lottery

[rzamora]

Hi, I am hoping someone can help me out with a problem I am having. I am new to alteryx and trying to conduct a weighted random lottery. I know that I can use the rand() function to get random numbers assigned to my individuals, and then rank them by their generated number, but the problem is I need some of them to have a higher chance of being selected. I basically have about 200 individuals but can only select 100 in the lottery. Because it is based on certain criteria some individuals might have similar weights. If I were to assign random numbers (between 0 & 1) then multiply it by their weight, that wouldn't really be weighting it properly and increasing their chance in the lottery, it is only doing it after the fact (right??). 

Here is an example of inputs:

Student A: weight = 1.45

Student B: weight = 1.45

Student C: weight = 1.25

Student D : weight = 1.15

Student E: weight = 1

Is this something that can be done in alteryx? Or, am I missing something in this formula? I admit it is kind of jumbled in my head. Any advice is much appreciated. 

[Luke_C · Accepted solution]

Hi @rzamora 

There may be better ways, but here's how I'd do this using an iterative macro:

1. Multiply the weights by 100 (1.45 > 145). Create that amount of records for each student (so 1.45 weight becomes 145 records/entries to the lottery).
2. Then the macro will randomly select 1 winner, remove all their entries from the data, and pass the remaining records through again until the desired number of winners is reached. The macro configuration lets you set how many winners you want to get. 

Hope this helps

[rzamora]

Wow! Thanks  this is perfect! Way more finesse than what I was trying.