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

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.

Workflow

Input

Fuzzy Match

Accepted answers

caltang

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

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

All comments

Qiu

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

caltang

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

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703