Get a dynamic number of (different) "Random" numbers per record

[Claje]

Hi,

This is kind of a weird problem that I haven't had to think about before.

I have a use case where an individual record has a number associated to it, called Y from here on, and we want to pick N random numbers between 1 and Y, where N is defined as CEIL(Y*.3).

However, we don't want to pick the same number twice.

Is there a way to do this kind of sampling, where we pull a number out of the pool?  I think an iterative macro might work but it feels like that might also be overkill.

Thanks!

[danrh · Accepted solution]

Here's kind of a hack solution :)

I'm using the Generate Rows to create Y*10 records to give myself a REALLY good chance that when I use RANDINT I get at least Y*.3 number of unique values (I googled how to say the ratio and it's something like 1 in a "quinquavigintillion".  Changing the number from 10, up or down, creates a tradeoff between the speed your workflow will run at and the chance of failure).  Then I use a Unique to make sure there are no duplicates and a Filter to grab the first CEIL(Y*.3) records.  Because Alteryx tries to be helpful and sometimes pre-sorts your data, the RecordID and Sort tools ironically maintain the randomness.

Hope it helps!

[Claje]

That's a pretty cool "Hack" solution.

I ended up using something pretty similar, but after generating the rows for Y records I assigned each number a random variable via RAND and used a Sort on that random value, as well as a Multi-Row formula to create a recordID for each individual record, for scalability.  Then I used a filter (similar to yours) to filter out anything beyond the N records I wanted.

I think this still maintains the random requirement of my sample while also ensuring no duplicate records will ever be selected.

The hard part, and the piece that took me so long to figure out, was the concept of using a sort ON a random value to select randomly.  It's definitely counterintuitive.