In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Create a field with a Random number with no duplicates

jdelaguila
8 - Asteroid

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

3 REPLIES 3
jeff_reynolds
10 - Fireball

Would something like the attached workflow do what you need?

MichaelLaRose
10 - Fireball

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
17 - Castor
17 - Castor

Hi @jdelaguila 

 

Here's my contribution:

 

Thableaus_0-1591911844571.png

 

- 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,

Labels
Top Solution Authors