How to create an adjacency matrix?

[geopil]

Hi all,

I have a problem that I am trying to solve with Alteryx.

I have a table with a list of providers and a list of services and a binary content indicating whether each provider is offering each service.

I would like to create a matrix, with the providers on both axes and the content showing the number of common services offered.

Has anyone built something similar?

Thanks

[jdunkerley79 · Accepted solution]

I am not quite sure if I am understanding the task completely but assuming an input like:

You can filter down just to the actual services provided by each provider (Filter Tool on Value = 1)

Then use a join to join the data to itself on Service

Next Apply a filter to the joined data, filtering Left Provider < Right Provider to create a unique set of common services between each provider.

Finally you can use a Summarise to create a concatenated field or a cross tab to create a matrix.

Sample Attached:

Concat output:

Matrix output:

 

Update:

As all you want is the count it is actually a little simpler. Tweaking the summarise and join and then using a crosstab to produce the required output.

No need for the filter as then it shows the number of services on a provider

[geopil]

It works!

Thanks a lot