Assign category for all the records with the percentage of allocation for each value.

[_VigneshRaja_]

Please assist to randomly assign the categories to the input data

Input

RecordID	Field 1	Field 2
1	User 1	100
2	User 2	200
3	User 3	300
4	User 4	400
5	User 5	500
6	User 6	600
7	User 7	700
8	User 8	800
9	User 9	900
10	User 10	1000

Category List

Category	Percentage of allocation
CAT001	20%
CAT002	30%
CAT003	10%
CAT004	40%

Output

RecordID	Field 1	Field 2	Category
1	User 1	100	CAT001
2	User 2	200	CAT004
3	User 3	300	CAT002
4	User 4	400	CAT003
5	User 5	500	CAT001
6	User 6	600	CAT002
7	User 7	700	CAT002
8	User 8	800	CAT004
9	User 9	900	CAT004
10	User 10	1000	CAT004

[Qiu]

@_VigneshRaja_ 
This is a very challenging one...

Hope someone can crack it. 😁

[SPetrie · Accepted solution]

I took a shot at it and here is what I came up with. It gets a bit fuzzy with odd numbers of records or percentages, but it may get you close to what you are looking for.

I have it figure out how many records are in the main data and then use the percentages in the allocation chart to figure out how many rows are needed for each category. I generate the number of rows needed based off those numbers so I have the correct (or close to correct) number of rows to assign to the main data.

I also assign a random number to each row of the main data and then sort it by the random number so it scrambles up the data. I use a join on position to add the categories to it and then sort back to original order. 

We get random assignments each time its run. 

.

[flying008]

Hi, @_VigneshRaja_ 

Maybe you like this way: