Create a field with a Random number with no duplicates

[jdelaguila]

I have a file with 1 million records in it. I need to create a field that is populated with a random # based on the size of the file - so in this case it would be from 1 to a million.  Also there can't be any duplicates within that field.  I tried the RandomInt within the Formula tool - but it created duplicate numbers. Any thoughts on how i could do this?

My end goal is to use this field to randomize all my data going into a Merge Purge. 

Javier

[jeff_reynolds]

Would something like the attached workflow do what you need?

[MichaelLaRose]

Hi @jdelaguila ,

I have attached a workflow that identifies how many records you have in your dataset, and sequentially creates IDs from 1 to N starting at a random value. The trick is to take the modulus of your number of records to reset the count to 0 when you exceed the number of records in your dataset.

The ID is dependent on the sort of the data that enters so it isn't a random ID.

Best,

Michael 

[Thableaus · Accepted solution]

Hi @jdelaguila 

Here's my contribution:

- You'd add a RecordID

- Use the select tool to isolate the RecordID field

- Create a RandomSortNumber field, using the Rand() expression

- Sort by this field

- Use the Join tool and join by record position back to your dataset. Rename the Right_RecordID to your new RecordID.

Workflow attached.

Cheers,