How to create an adjacency matrix from the edge list (special case)?

[EugeneY]

I need help with creating an Alteryx project to

transform an edge list like an example below (see in Input tab of the attached file):

Account	TransactionID
3000003Ct	20134000000001
3000017Dt	20134000000001
3301102Dt	20134000000001
3000003Ct	20134000000002
3000016Dt	20134000000002
3301102Dt	20134000000002
3000003Ct	20134000000003
3000016Dt	20134000000003
3301102Dt	20134000000003

into adjacency matrix like the one below (see in Output tab of the attached file):

	3000003Ct	3000017Dt	3301102Dt	3000016Dt
3000003Ct	0	1	3	2
3000017Dt	1	0	1	0
3301102Dt	3	1	0	2
3000016Dt	2	0	2	0

Thank you

[Thableaus · Accepted solution]

Hi @EugeneY 

See this solution:

- Use Summarize Tool to group by Transactions and Concatenate all Accounts

- Join the original dataset to the concatenated accounts for each Transaction ID (join by Transaction ID)

- Use Text to Columns to Split to Rows, so you have accounts connected in a row level

- Group by and count each relationship between accounts

- If you are comparing same accounts, that's gotta count as a 0, so we use formula tool.

- Cross-Tab Tool to set up the matrix

- Data Cleansing Tool to transform Nulls in 0's.

Workflow attached on version 2018.4

Cheers,

[EugeneY]

Thank you, Thableaus.

Your code produces expected results.

I appreciate your help.