Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

How to randomly select samples from population based on the percentage each group weights?

Highlighted
6 - Meteoroid

Hi there,

 

Good evening.

 

I am trying to get 5 samples from a population which contains 3 user and 10 records in total. Please see below.

 

 

Record IDUser IDCredit Card Expiration Date
1A7/7/2019
2A8/8/2020
3A9/9/2018
4A1/3/1900
5B7/7/2019
6B8/8/2020
7B9/9/2018
8B1/3/1900
9C7/7/2019
10C8/8/2020

 

User A has 4 records and weights 40% in the total population;

User B has 4 records and weights 40% in the total population;

User C has 2 records and weights 20% in the total population;

 

Since I need to take 5 sample from this population, I will need to select

 

2 samples from User A

2 samples from User B

1 sample from User C

 

How can I do this with Alteryx?

 

Any of your inputs will be extremely helpful! Thank you in advance! 

 

Best,

 

M

 

Highlighted
Inactive User
Not applicable

Sort on User ID. Then use Sample, 1 in N where N is 2. This will give you the 2 2 1 result you are looking for.

Highlighted
6 - Meteoroid

hi Ryan,

 

thank you for your reply.

 

your solution works for this particular case; however, as the data will change, this solution will not work. so i guess i am looking for a solution that can sample different groups of record based on the percentage they weight in the total population.

 

best,

 

M

Highlighted
Inactive User
Not applicable

Sum number of records, append that on. Then group by the ID and count. Then divide that count by the total. That gives you the percentage. Take that percentage and join back on by ID. Then create a batch macro and the Batch would be the ID, and the value for the batch is the percentage. That percentage is then used in the sampling tool to dictate how many records to take. The batch macro will then union the outputs and away you go.

Highlighted
6 - Meteoroid

Hi Ryan,

 

Good morning. 

 

Thank you for your help! 

 

I am not very familiar with batch Macro. Could you show me the workflow if that is not so much trouble to you? 

 

Thank you!

 

M

13 - Pulsar
13 - Pulsar

What you're trying to do is a random sample based on a grouping.  I often have this need and created the attached macro a long time ago.  It's basically just spin on the already existing macro "Random % Sample".

 

Save this to your Alteryx Macro location, which is most likely "C:\Program Files\Alteryx\bin\RuntimeData\Macros".  The new macro "RandomRecords_Grouped" will then show on the Preparation palate of your Designer.

 

For your example, you'd select a random 50% of your records, and check the "User ID" field for the "Select random records for each value to the following field(s):" portion of the configuration.

 

Let me know if that works.

Labels