Forum Discussion

SharonHMA's avatar
SharonHMA
Helper I
9 years ago
Solved

How to Create Random, Unique, Alphanumeric Data in a Field

I need to move some data from one system to another.  The second system requires a Unique ID field that is 6 digits long, alphanumeric.   -my current data comes from two sources and does not have a...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    HI SharonHMA,

     

    You can try to use below formula to create guid column, the max count of characters combo is 308,915,776 (26^6).

    GUID = UPPER(UNICHAR(RANDBETWEEN(97,122)))&UNICHAR(RANDBETWEEN(97,122))&UNICHAR(RANDBETWEEN(97,122))&UNICHAR(RANDBETWEEN(97,122))&UNICHAR(RANDBETWEEN(97,122))&UNICHAR(RANDBETWEEN(97,122))

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
9 years ago

HI SharonHMA,

 

You can try to use below formula to create guid column, the max count of characters combo is 308,915,776 (26^6).

GUID = UPPER(UNICHAR(RANDBETWEEN(97,122)))&UNICHAR(RANDBETWEEN(97,122))&UNICHAR(RANDBETWEEN(97,122))&UNICHAR(RANDBETWEEN(97,122))&UNICHAR(RANDBETWEEN(97,122))&UNICHAR(RANDBETWEEN(97,122))

 

Regards,

Xiaoxin Sheng

Sokon's avatar
Sokon
Advocate V
8 years ago

The big amount of possible values just makes duplicate values extremly unlikely. But it does not really guarantee unique values by design, right? Any idea how to accomplish that?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Sokon,

     

    Maybe you can try to enter to query editor and use Text.NewGuid function to create unique guid.

    Text.NewGuid

     

    Regards,

    Xiaoxin Sheng

    • Sokon's avatar
      Sokon
      Advocate V
      8 years ago

      jthomson: Thanks for the idea! Using that approach I'd lose the random aspect, because knowing the rule, we could restore the original data, right?

      Anonymous: Thanks for the hint, too! I didn't know about that function. Yes, with that i should be able to create a list of distinct values i want to anonymize and attach a guid to them. Then I could use the result as a mapping table possible that can be used to replace the original values. I'll give it a try!

       

      Thanks for your help!