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

  • SharonHMA's avatar
    SharonHMA
    Helper I
    9 years ago

    Awesome thank you works like a charm!  And yes I know I won't run out with this which is why we chose that for the new system.

  • 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!

  • WLTozon's avatar
    WLTozon
    Regular Visitor
    8 years ago

    Can you help me? I dont understand this function. I am need do a Randbetween, but the number can't repeat.

     

    Example: 

     

    Workers:

     

    Name    ID

    João      1

    Maria    2

    José       3

     

    Product:

     

    Name        ID      RANDBETWEEN(Workers)

    Rice           1                      1

    Orange      2                      2

    Milk           3                      3

    Coke          4                      1

    ......             5                      2

    ........            6                     3

     

    I have a problem, because the function RandBetween can repeat the same number any times

     

    Can you help me?

     

    Thanks!!