topic Re: Assigning Random Number to a Data Set to Form Even Pots in Alteryx Designer Desktop Discussions

Assigning Random Number to a Data Set to Form Even Pots

Pav007 — Thu, 13 Jun 2024 22:20:05 GMT

Hi,

I am struggling to assign a randomly generated number as a separate column to my data set in such a way that it would give me even splits as a final output.

For example, I have 900 rows in my data set and would like to randomly assign numbers between 0 and 8 to it as a separate column so my final output will be

0 = 100 rows

1 = 100 rows

2 = 100 rows

3 = 100 rows

4 = 100 rows

5 = 100 rows

6 = 100 rows

7 = 100 rows

8 = 100 rows

I was able to add random numbers to my data set as a separate column using RandInt(8) function , however the final outputs are not 100 rows per number - it varies between 98 and 134 rows. RandInt(8)Splits per Number

Apologies if this is a dumb question.

Many thanks,

Pav

Re: Assigning Random Number to a Data Set to Form Even Pots

jdunkerley79 — Thu, 18 Oct 2018 10:33:35 GMT

This should do roughly what you need.

I create a random string (UuidCreate()) and then sort on this.

Then add a record ID and divide by the size of required groups

Finally, use this to assign the groups

Hopefully attached will give you some ideas to build on top of

Re: Assigning Random Number to a Data Set to Form Even Pots

JoeS — Thu, 18 Oct 2018 11:21:00 GMT

James' solution is neat and nicely dynamic.

If you wanted something a little more simple you could also look to use the rand function along with a sort and tile tool in equal records mode.

Re: Assigning Random Number to a Data Set to Form Even Pots

Pav007 — Thu, 18 Oct 2018 11:31:51 GMT

Re: Assigning Random Number to a Data Set to Form Even Pots

Pav007 — Thu, 18 Oct 2018 11:35:49 GMT

Re: Assigning Random Number to a Data Set to Form Even Pots

Pav007 — Thu, 18 Oct 2018 11:32:35 GMT

Thank you for your reply. I've managed to achieve what I need based on the suggestions you guys provided.

@jdunkerley79 wrote:
This should do roughly what you need.

I create a random string (UuidCreate()) and then sort on this.
Then add a record ID and divide by the size of required groups
Finally, use this to assign the groups

Hopefully attached will give you some ideas to build on top of

Re: Assigning Random Number to a Data Set to Form Even Pots

Pav007 — Thu, 18 Oct 2018 11:34:39 GMT

Wow guys! I've just joined the community today and you've already impressed me. Thank you very much Joe! This is exactly what I've been looking for.

@JoeS wrote:
James' solution is neat and nicely dynamic.

If you wanted something a little more simple you could also look to use the rand function along with a sort and tile tool in equal records mode.

Re: Assigning Random Number to a Data Set to Form Even Pots

JoeS — Thu, 18 Oct 2018 11:37:28 GMT

@Pav007 wrote:

Wow guys! I've just joined the community today and you've already impressed me. Thank you very much Joe! This is exactly what I've been looking for.

Great stuff, I am glad you are already getting the answers you require, long may it continue......