UuidCreate() -- defining parameters for the unique identifier

[OmaratRJL]

Hello

I have the following use case I need to define a 6 character identifier for about 1.2 million records. Using the UuidCreate() gives me something that looks like this: 

d24eca0a-50c1-4a09-8e09-783abff272a1

I am 100% that this will insure uniqueness but it needs to be usable and this is way too long. So I was wondering if there is a way to get Alteryx to trim the string while the function is working and also do some extra things like: 

eliminate the letters o, i ,l to increase readability and accuracy. 

If I trim the string to 6 characters I get roughly 36,000 dupes.

Any ideas on how to tackle this?

[jdunkerley79 · Accepted solution]

While not guaranteed to be unique, how about:

IntToHex(RandInt(HexToNumber("FFFFFFFF")))

This will generate a random hex number of up to 8 characters long

The chance of collision will be high at this level, if you allow 12 then it will be much lower.

An alternative approach would be to randomly order the records and add a record ID. This would guarentee uniqueness

[OmaratRJL]

Thank you! 

1124065 still has 38441 dupes.

IntToHex(RandInt(1600000000000000000000000000000000000000000000000000000000000)) gives me 311 dupes 

Any suggestions?

[jdunkerley79]

Looks like RandInt isnt random enough!

If 12 characters long is ok the last 12 of a UUID is sufficient in my quick testing to not get collisions (no guarentees tho):

right(UuidCreate(),12)

You could try to re-encode the bytes into more than hex if you want it shorter (10 characters if you have a 32 character set) but this would be hard to write in a single expression.

[OmaratRJL]

My goal was to keep the string readable. So that it could be quickly communicated. 

I think i can do some regex to remove the I and O. 

I do appreciate that answer and i will work it up into a solution. 

I think for my purposes IntToHex(RandInt(160000000000)) seems to give me the least dupes. 

[jdunkerley79]

For what it's worth a UUID is hexadecimal so if you upper case it would just be 0-9 and A-F but glad you have a solution.

[estherb47]

Hi @OmaratRJL 

To generate a unique hexadecimal, you might create a quick workflow/macro that does that. According to this website, http://mathcentral.uregina.ca/QQ/database/QQ.09.00/churilla1.html, there are over 2 million combos.

You'd need to generate 26 letters, and 10 digits, and then figure out all combinations of the two.

Hopefully that gets you started! If I have the time, will work something up. Perhaps @jdunkerley79 can take the reins again?

Cheers!

Esther

[OmaratRJL]

Luckily I only have one batch to actually create.

If I had to consistently create identifiers and match them up against previous ids I think it would be worth it but for now I think I am good.