Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

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

Random Sampling Formula

ekarmanov
7 - 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

5 REPLIES 5
chris_love
12 - Quasar
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
MarqueeCrew
20 - Arcturus
20 - Arcturus

@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 restart. Order shall return.
Please Subscribe to my youTube channel.
ekarmanov
7 - 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

chris_love
12 - Quasar
Thanks Eugene - I didn't even know about the Bound function!
marlline
8 - 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