Set Analysis - looking for Dupes

[#1]

I have been struggling with a problem for a day or so on how to do the following:

I have a table with Securities Keys (SEC_KEY) and CUSIPS (CUSIP) with possible many-to-many relationships. 

I want to create a chart that displays that answers the following question:

For each distinct SEC_KEY how many other distinct SEC_KEYs have at least one CUSIP in common with it.

So, if my data was as follows:

SEC_KEY        CUSIP

A                         1

A                         2

A                         3

A                         3

A                         4

B                         1

B                         1

B                         5

C                         2

D                         2
D                         2
D                         4
D                         5
D                         6
E                         7

F                         3

The output chart would be:

A                         4  (for B, C, D & F)

B                         2 (for A & D)

C                         2 (for A & D)

D                         3 (for A, B & C)

E                         0

F                         1 (for A)

[#2]

I knew this was a bit tricky but still think quite doable - surprised by striking out in terms of any response - maybe I can ask QT to assign bonus points?  Any help on this is greatly appreciated!

[whiteline]

Hope I understand your problem correctly...

Suppose your table named SECDATA contains two fields SEC_KEY and CUSIP.

Add the following to the script:

LOAD Distinct

     CUSIP

      SEC_KEY as SEC_KEY_COUNT,    

Resident SECDATA;

Then add chart with SEC_KEY as dimension and =Count(distinct SEC_KEY_COUNT)-1 as expression.