Trying find similarities then graph a representation

[Astraithious]

Hello! I am new to alteryx and have been getting some amazing help here so I figured I would ask for some more.

I am now using some data from a video game I used to play as I wanted to see the inner workings and how things work. 

Essentially players control a character and that character has an ID called "GUID" and tied to it are various spells and abilities. 

I created a list of every GUID and then the spells and abilities tied to it now I want to group them up by the GUID to show everything they have then compare each one of the GUIDS to an another to see how similar they are.

My vision was to be able to see how similar GUID picks are to one another which is different than taking the entire population of unique GUID then simply counting up how many each spell was taken saying x people took y which is z% of the total pop. 

I have never done it before but i guess the closest thing is like making each GUID a fingerprint then matching it against another to see what matches and what doesn't but do that for all of them to see how much similarity there is. 

I might be dreaming though for what can be achieved however if anybody could steer me in the right direction that would be amazing, thank you for your time

[SeanAdams · Accepted solution]

Interesting challenge @Astraithious,

What I've done is to build a very simple similarity score which is essentially "What portion of the the spells match, and what portion don't"

So if A and B both have all the same spells, then the similarity is 100%, but if A has 2 spells and B only has one of them, then the similarity from A to B is only 50%.

Have a look - this should give you a start to play with.

Cheers

Sean

[Astraithious]

That is a fantastic method! Would you mind explaining the steps a bit more though? Why you ran them through the join and such. Thank you!

[SeanAdams]

No worries at all.

The basic thinking is that I want to get two pieces of data:

- For each user: How many spells do they have in total

- For each user: How many spells do they have in common with user X

So, for the first part, this is easy - just summarise

For the second part - a bit tougher:

- Because you want to know the spells which are common between user X and user Y - you can take the input data, and join it to itself based on spell.

     - this explodes out the data because if X has spell 1, and so do 20 other people, then this one row becomes 20 rows.

- Next you want to eliminate from this data where X with spell 1 has joined to X with spell 1.    the reason why is that of course x has the same spells themself - but you're looking for when user X has the same spells as someone else.

- Then eliminate duplicates.   That's a good habit.

...at this stage you have a list with a user; with a spell; and then another user who has this same spell

Now what you want to do is to summarize this up so that you can say "How many spells does user X have in common with user Y".   this is done with a summarize

So - where are we now?

Now we have 2 data sets:

- One says "User X has 10 spells overall"

- The second set says "User X has 5 spells in common with User Y; 7 spells in common with user Z; etc"

If you join these two data sets together based on the user ID (GUID) then you have one data set

Finally figure out the overlap

Then you just use a formula to find overlap

- User X had 10 spells, and 5 overlapped iwth user Y; so user Y is 50% the same (5/10)

- User X had 10 spells, 7 overlapped with user Z, so user Z is 70% the same (7/10)

Hope this makes sense :-)

Have a good week

Sean

[rohanonline]

It's an interesting problem. Great solution @SeanAdams.

I have taken a different approach.

I am not sure of the ultimate objective, but the way this is different than @SeanAdams is that it matches if both GUIDs have similar spells. For e.g. the top matching GUIDs in the other approach (99.3% similarity) are "13690" and "10181". However, "10181" has 3659 spells while "13690" has only 145.

Keen to know your thoughts. You can definitely lower the correlation bound in case you want to see more GUIDs.

[SeanAdams]

Clever idea @rohanonline - thank you for putting this up.    I agree that your method does measure the relationship better because it's symmetrical (it's measuring similarity between A & B; not how much A is like B which mine does).

Appreciate you posting this - I learned from it!

[Astraithious]

Hello! I was looking at this and couldn't quite grasp the results. I only get 16 results there but I want to match up everybody against everybody to see how similar the spell set might be, then display the spells that they have similar. That would be the best outcome but I do not know how to get there yet. Can you explain your method to? Thank you!