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

Question

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

AlteryxInputOutput.xlsx

Thableaus · Accepted Answer

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,