Select Lottery Winners based on Set of Odds

Question

Hello - I have a randomization/lottery problem that I need some help with. I have 2 datasets (sample data attached):

* A List of customers
* A List of products, available inventory and odds of winning each product (expressed as a %)

My desired output is the same list of customers with an additional column that notes which product they won (if any). A few constraints / notes:

* Not every customer needs to win something
* Once inventory is exhausted for that product, customers can no longer win it (i.e. if product A has 10 available, only 10 customers can win it at most)
* Not all inventory needs to be used

I've been trying to figure out the right way to run this in Alteryx but not sure on what the right way to approach/start is. Would appreciate any help here. Thank you!

SampleData.xlsx

isabella201 · Answer

Interesting challenge! You might try a weighted random selection process where each product's odds are used to calculate a win chance per customer, and then iterate through the list while tracking inventory limits. It’s kind of like asking When Is The Next Powerball Drawing? you need controlled randomness with fixed constraints. Alteryx can handle this, but you'll likely need some combination of batch macros and multi-row formulas to manage inventory depletion.

kboeckholt · Answer

Here's my solution. A couple of assumptions I made 1) Customers can win more than one prize and 2) Inventory is handed out in order of Entry No.

Input with Odds:

Formula:

Output:

LottoPrizes_KB.yxmd

FrederikE · Answer

Hey,

I'd go with something like this.

RandomPrices.yxmd