2 Replies Latest reply: Aug 18, 2010 6:05 PM by John Witherspoon

How to count the number of matching records?

I have 3 lists of names titled 'swimmer', 'runner', biker.

I'd like to see how many names overlap in each list.

I'm trying to use a COUNT function with set analysis, but it's not working out well in my straight table. Any advice?

Thanks...

• How to count the number of matching records?

The easiest way i could think to do it was to pre-build a table in the load script to flag and count the activity.

First of load the three columns (swimmer,runner,biker) into a table called "masterlist"

`Athletes://flag swimmersLOAD swimmer as athlete, if(not isnull(swimmer),1) as swimmerresident masterlist;//flag runnersconcatenate LOAD runner as athlete, if(not isnull(runner ),1) as runnerresident masterlist;//flag bikersconcatenate LOAD biker as athlete, if(not isnull(biker),1) as bikerresident masterlist;drop table masterlist;`

That should give you a table called Athletes with a distinct list of names a 1 in each catagory they apply.

Your straight table chart should have 1 dimension - athlete and possibley 4 expressions e.g.:

expr1 - sum(biker)

expr2 - sum(runner)

expr3 - sum(swimmer)

expr4 - sum(biker)+sum(runner)+sum(swimmer)

the last expression will effectively tell you how many lists the person is on.

You can also expriment with expressions like :

if(( sum(biker)+sum(runner)+sum(swimmer))=2,1)

which will flag a 1 if the eprson is on two lists, set the expression total to sum of rows also.

• How to count the number of matching records?

If I understood the question, -sum(Swimmer=Runner and Runner=Biker). See attached.