Counting matching columns which have "1"s in each row

[scottwbarlow1]

Hello!

I have a database of items which users have selected, and I would like to create a table which summarises the relationships between each item (i.e. a table which shows how many users have selected both items (items in  rows and columns).

My input table is attached and looks like the below sshot, except for 'user_id', all columns reference an individual item (e.g. 536, etc):

My desired output is a table like the below which counts the number of users who have selected both items (add a column to the right which will provide this count):

Please can someone guide me on how to build this count function? I have got lost in the "transform" section, i'm sure the answer is there.

The overall purpose of this exercise is to provide the 'edges' for the network analysis tool (but I don't need help with that, yet :) )

Thanks community!

Scott

[binuacs]

@scottwbarlow1 Looking for something like below

[scottwbarlow1]

Not quite. I am looking to count how many users bought each pair of items. For example 1,973 users purchased both items "536" and "786" (first two columns in the first document).

Ignore the '1's I put as a placeholder in the "Count" column.

I'm attaching an example of how I calculated the above example, but I'd like to do this for all pairs of items in the output document.

Thanks

[binuacs]

@scottwbarlow1 I thought of you want 1 t be included in the output, yes it would be great if you provide your calculation at least for one book id

[scottwbarlow1]

Example forthe first pair of items attached to my previous reply. Thanks

[Felipe_Ribeir0 · Accepted solution]

Hi @scottwbarlow1 

Please take a look at the attached workflow, if i understood well the problem, this is a possible way to solve it