Selecting random records to achieve a specific average

[FrankieChai]

Hi,

I have a file of 550,000 members along with their birth date and I need to randomly select 53,000 records from it.  The caveat is that I need the average age of these 53,000 records to be 53.  Is there a way I can do this in Alteryx?

Thank you

[CharlieS]

Here's how this could be done:

- First, calculate the difference in age that each member is from 53.

- After that is calculated, then you could randomly pair people together that average out to 53. For example, pair a 50 year old with a 56 year old.

- Now that the random pairs are generated, select 27,500 pairs (55,000 people) for the final output. 

This process would require some batch/iterative macros, but it's all very possible in Alteryx. 

[benakesh · Accepted solution]

Hi @FrankieChai ,

This is interesting problem !

This is my sample data .

field1 ( name)  field2( age)  

a 1
b 2
c 4
d 5
e 3
f 2
g 5
h 5
i 2
j 1

The attached  wf  calls iterative macro  to pick 5 random records with average = 4 .  The maximum number of iterations is  100 .  

Hope this helps  . 

[FrankieChai]

Thank you Benakesh for the solution.  This work beautifully.  Thank you!!