Adjacency Matrix

[TR]

Hello Community Friends,

I'm interested in automating the results for an adjacency matrix output. I've attached a sample spreadsheet to assist with what I'm attempting to automate. It seems so simple, but I've not figured out a shorter/easier way to do this. 

Adjacency Matrix tab - includes a formula used to calculate the values from Sheet 1. I'm interested in populating this sheet without using the SUMPRODUCT formula. To help, I've populated the first 3 rows to show what the output results should be.

Thanks in advance!

Todd

[kelly_gilbert · Accepted solution]

@TR - the easiest way would be the Market Basket Affinity tool.

It's part of the Alteryx Predictive Tools set, which are a separate download... You can get them here (just make sure to download the same version as your Alteryx Designer install, which you can find via Help--> About -- match the version number and admin/non-admin).

First, some basic data prep to transpose the columns into rows, get rid of nulls, and remove the brackets around the column headers.

Then, in the MB Affinity tool, select the original column A as the "transaction", and the original column headers as the "item," and the Affinity measure = Co-occurrence.

If you can't install the predictive tools for some reason, you can (depending on the size of your data!) do the brute force version of generating every possible to/from combination, and then use the Crosstab tool to generate the table.

[TR · Accepted solution]

Hey @kelly_gilbert this is great (and way shorter than the hot mess I was working with). I went with the Method 2 option.

Curious about your thoughts on a couple of tool uses.

1. !IsNull Filter: as it does not have any false records.
2. Multi-Field Formula: replacing nulls with zeroes vs using the Data Cleanse Tool.

Definitely understand we all create/solve workflows differently so I'm interested whether it's preference etc.

Second, I added a Sort (Ascending by first_node) before the Browse. This shows the matrix with the main diagonal (first_node = column name). How could I set those diagonal values (e.g., row X357, column X357) to zero?

Thank you! 

[kelly_gilbert · Accepted solution]

@TR  - 

The 2nd isnull filter (after the Join) is unnecessary (just a remnant from copy and paste!)

Re: Multi-Field vs. Data Cleansing...

First of all, I find the Data Cleansing tool to be a bit slow, so there is a trade off between ease of configuration and performance. If I have a small dataset and lots of cleansing operations to do, then Data Cleanse may make more sense. If I have a large dataset and only one change to make (as we do here), then I'll go with Multi-Field.

The big reason for this particular use case, though, would be that the Multi-Field Formula tool has a Dynamic or Unknown fields option. If that's checked, it will automatically apply the change to any new columns that show up.

Data Cleansing doesn't have that option, so if new columns showed up in the data, you'd need to go in and select them.

[TR]

Spot on! You are absolutely correct. And I usually tend to go back and update those tools instead of using the mult-field formula tool with the dynamic ability. You've changed how I'll do this in the future, thank you!

[kelly_gilbert]

@TR - also, I realized that I totally forgot to answer your question about forcing the diagonal (node1 and node2 are the same) to zero...

You can do that by adding a condition to the Multi-Field Formula's expression:

Note that you will need to move the Dynamic Rename before the Multi-Field (so the "Count_" is removed from the column names first).