Need to segment the input file by randomly assigning a group ID

[sraja]

Hello All,

I have two input files and I want to create groups based on a Time Zone field. For example- File A has 150 records and File B has 20 records. I want to create 20 groups and allocate the 150 records to each of these groups, so will have approximately 7 in each group. Please advise on how I can accomplish this. I want to randomly assign 7 records from File A and associate the group # to a row in File B.

Thanks.

[Qiu]

@sraja 
Can you provide a set of sample input and output data?

[caltang · Accepted solution]

You can use the RAND() expression and then MOD() it to 20, then whatever is left will be considered as group 20.

You can summarize once the values are present.

[sraja]

Can you please explain further on how to use mod and rand function, as the rand function will return values between 0 and 1

[sraja]

Please see below:

File B:

Group	Name
1	11111
2	22222
3	33333
4	44444
5	55555
6	66666
7	77777
8	88888
9	99999
10	111110

In File A, I want to randomly assign 7 "Name" to a group.

ID	Name	Group Assigned
1	AAA	1
2	BBB	1
3	CCC	1
4	DDD	1
5	EEE	1
6	FFF	1
7	GGG	1
8	HHH	2
9	III	2
10	JJJ	2
11	KKK	2
12	LLL	2
13	MMM	2
14	NNN	2
15	OOO	3
16	PPP	3
17	QQQ	3
18	RRR	3
19	SSS	3

[sraja · Accepted solution]

I was able to resolve the issue by using a formula using rand() and apply sorting on this new column and have another formula with ToString(Mod([ID] - 1, 20) + 1).