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

Comparing hashes

KaiLarsen
9 - Comet

I'm trying to compare hashes to calculate a number indicative of their level of similarity. Any thoughts about how to compare these strings? The strings are same length and identical hex in the same location indicates similarity. For example, hashes 3 and 4 both start with 'e', so +1 for their similarity. Their positions 7 and 8 are also identical, so +2.

 

329036933693649764956c996699e6996b18cf1ce704f719ff19ff19bf39bf3c
eff86ff06fe067f0e5c0adc0bd8a9f88df98de885e409ed0dfd047dceff34fe1
ea31de34b874b424be29ee39eb21ed216c616c69c8c9ce49cc61cc39c3c9cdc9
e336e134f070e131e011e210e014e03de831e03af036f058f019f1317907fd83
870ff603e801e965b8c931e9a1cda1cda58d81e580e5844d880d998509cd036d

 

Thoughts?

10 REPLIES 10
jdunkerley79
ACE Emeritus
ACE Emeritus

I would break it into character rows.

 

You can then join it to itself and do a count.

 

Process something like:

2016-07-06_09-05-24.jpg

- Add a RecordID

- Use Regex to Tokenise

- Use a multi-row formula to add a CharacterID

- Join self on CharacterID

- Filter so left RecordID < right RecordID and characters equal

- Summarise to group by recordids and count.

 

 

KaiLarsen
9 - Comet

This is an impressive setup, @jdunkerley79. Thanks so much.

 

Kai :-)

KaiLarsen
9 - Comet

Alas, while elegant, the solution is not practical in my case. I have ten million hashes. Multiplied by 64, that gives me more than half a billion records to join with the same number of records. Do you have any ideas for a more direct comparison of a hash pair? I'm wondering if the Fuzzy Match tool can be used somehow?

 

 

jdunkerley79
ACE Emeritus
ACE Emeritus

Thats a chunky problem - about 5x10¹¹ values to compute///

 

Think best approach is probably a new formula function and then should be a single c++ call for each function...

 

Will arr a charmatch function to my formula addin and see if works. Back as soon as have a result. Will be an interesting test of the engine...

KaiLarsen
9 - Comet
I think I may not have explained the problem well enough. Sorry for any confusion.

Essentially, each row consists of two 64-character hash values, and I need to loop through to compare and count the number of matches, for example:

For x= 1 to 64
If hash1[x] = hash2[x] then count=count+1

This would be trivial and fast in R, but I'm trying to learn Alteryx better, so trying to resist the R-node.

Kai :-)
MarqueeCrew
20 - Arcturus
20 - Arcturus

@KaiLarsen & @jdunkerley79,

 

I came up with a comparison that essentially doesn't require a JOIN, but does explode columns to rows.  It is fairly efficient in that the join that I do use is based upon record position.  I understand the simplicity of the array loop.  Please do check the timing for this module as I'm interested in knowing if it does improve upon my colleague's solution.

 

Thanks,

Mark

 

Screen Shot 2016-07-09 at 2.28.52 PM.png

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
KaiLarsen
9 - Comet

Thank you so much, @jdunkerley79. I was able to optimize it down to 35GB and three hours 9 minutes for the whole workflow, which is almost reasonable for this one-off task, but as soon as I'm done with this task on Monday evening, I'm going to try your solution out.

 

Joining based on position sounds promising, though.

 

Kai :-)

jdunkerley79
ACE Emeritus
ACE Emeritus

Ah had misunderstood.

 

A much simpler problem. I  did the following:

Generate a set fo CharID row from 1 to length of the hash.

This will scale it up to 64x original size.

Then Filter where characters at position match

Final group back together counting rows.

 

2016-07-10_10-03-52.jpg

 

Basically same as for loop, I havent dealt with 0 similarity in this case but that could easily done.

 

Adjusted flow now looks like:

2016-07-10_14-01-30.jpg

This copes with 0 matches and puts back in input order

cupcaketayla
5 - Atom

@jdunkerley79 wrote:

I would break it into character rows.

 

You can then join it to itself and do a count.

 

Process something like:

2016-07-06_09-05-24.jpg

- Add a RecordID

- Use Regex to Tokenise

- Use a multi-row formula to add a CharacterID

- Join self on CharacterID

- Filter so left RecordID < right RecordID and characters equal

- Summarise to group by recordids and count.

 

 


 

Labels