Assigning Random Number to a Data Set to Form Even Pots

[Pav007]

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

[jdunkerley79]

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

[JoeS · Accepted solution]

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.

[Pav007]

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

---

[Pav007]

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.

---

[JoeS]

---

@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......