Forum Discussion
Generate unique ID from various columns
Hello,
I have 8 columns of financial system account data (approx 45 digits total). I need to generate a unique ID of 8 digits for each unique combination of the other 8 columns.
Any suggestions?
Thanks
7 Replies
Fund 1000 1000
Function 03 03
Department 300 300
Division 5100 5100
Function 52210 52305
Subfunction 005 001
Location 5100 5100
Program 0000 0000
So, these two accounts differ by function and subfunction. However, I have thousands of accounts that differ on every one of the 8 account structures. I need an 8-digit "code" to identify unique accounts.
It's generally easier to create an index rather than a suitable hash value.
Also, be aware that restricting to 8 decimal digits limits you to no more than 1 million unique IDs, so you'll need to worry about hash collisions if your data is close to that size or larger.
I've used indices before - unfortunately, in this case, the 8-digit number needs to be a "shorthand" version of the longer account string so that it can readily identify the account. I shouldn't wind up with more than a few hundred of these IDs - no worries about millions.
Uniquely representing 8 different structures corresponding to 8 digits won't be possible unless all of these structures have 10 or fewer distinct values. Even if this is the case, you'd have to map, e.g., "52305" and "52210" to a single digit, in which case it won't be a shorthand since you cannot map both of these to "5".
