Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Condensing MD5 Hash to shorter numeric key/ID

Dan_Z
7 - Meteor

Hello!

 

I'm designing an application to transform excel reports for output to our Azure and Bigquery data warehouses. The app takes an excel file and creates a metadata table and data table linked on two ID constraints: a uniqueID field which will be populated based on the last load (find max, add 1 and keep going up) and a "ProfileID" field which is an MD5 hash of the incoming column name. This ID is currently up to 32 alphanumeric characters long: 

 

clipboard_image_0.png

My IT team are looking for a way we can turn this ProfileID in to a shorter key to improve indexing and querying.The shorter key cannot duplicate unless the profileID is the same. This would happen in 12 months when a profile is updated with 2020 data (new row, new ProfileUniqueID, same ProfileID). Upwards of 5,000 rows will be added to this table every year.

 

Any ideas out there? 

10 REPLIES 10
Dan_Z
7 - Meteor

@jrgo @danilang 

Thank you both for the insights! 

 

I played around a little and came up with a test solution that produced zero duplicates with 105k uniques. It was an adaptation where I took the first 10 characters of the original 32 character hash and used the HexToInt function after splitting in to rows. After concatenating, I trimmed the integer to 19 characters, converted to INT64 with no issues and no duplicates. (workflow attached).

 

My metadata table will max out somewhere around 5-6k profiles that will need these unique values (they need to repeat year over year as we update the profiles to a new data year), so if it worked for 105k I feel confident it will work for a tenth of that. 

Labels