Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Random Sampling Formula

Highlighted
Meteor

Hello,

 

Hoping someone can provide guidance/suggestions on how to best handle to following:

 

I have already performed a number of steps to arrive at my desired final population of records (numbered 1 through 89 in this case) using the Record ID tool.

Now I need to obtain a random sample from this population considering the following:

 

1. Determine sample size based on the parameter: 10% of population count (89) with a maximum of 25 and a minimum of 5

a. The required sample size should be 9 in this case. This sample size should always be rounded up to the nearest whole integer (e.g., even if 10% of population was 8.1, the required sample size would still be 9). 

b. I can't seem to determine a way for Alteryx to provide a basic RoundUp type formula/functionality like in Excel.

 

2. Have Alteryx produce a random sample based on the required sample size. 

a. The usual supects Sample and Random % Sample tools don't seem to have a way of inputing required sample size automatically.

 

Thank you,

Eugene

Alteryx Certified Partner
Alteryx Certified Partner
Hi Eugene

Here's the steps I go through.

1. Use a Count Records Tool to Count the number of records

2. Append this to the original data using the "Append" Tool

3. using Formula Tool: Multiply the total by 0.1 and use the Ceil()
function to Round Up.

4. Use and IF statement to apply the minimum and maximum - this is your
Sample Size

5. Use Formula Tool to Create a Rand() number

6. Use Sort Tool to apply a sort by the random number

7. Attach a Record ID using Record ID tool

8. Use Filter to find all rows where RocordID <= Sample Size

Happy to supply a module but since you are new I thought you might like to
try this yourself.

Chris
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@chris_love and I were very close.  I just completed the exercise and created a workflow for you.  We approached it in a similar way.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Meteor

Thank you, both, for the quick and excellent solutions.

I appreciated the opportunity to learn doing this on my own as well as seeing a clearly laid out solution.

 

The only (minor) difference that I was initially considering for the sample size calculation formula was to use a combination of Ceil and Bound formulas (i.e., Ceil(Bound(0.1*[Count], 5, 25)).

 

Best Regards,

Eugene

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
Thanks Eugene - I didn't even know about the Bound function!
Highlighted
Asteroid

I just realized that Random Sample Tool did not give me random sample at all. When I enter # of records, it might just give me first #. Thank you so much for sharing!

Labels