topic Re: Set Analysis - looking for Dupes in QlikView

Set Analysis - looking for Dupes

Wed, 22 Aug 2012 14:08:52 GMT

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 4

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)

Re: Set Analysis - looking for Dupes

Thu, 23 Aug 2012 16:13:59 GMT

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!

Re: Set Analysis - looking for Dupes

whiteline — Mon, 27 Aug 2012 18:16:21 GMT

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.