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

    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?



        • 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"

          the use this load script


          //flag swimmers
          LOAD swimmer as athlete,
          if(not isnull(swimmer),1) as swimmer
          resident masterlist;
          //flag runners
          concatenate LOAD
          runner as athlete,
          if(not isnull(runner ),1) as runner
          resident masterlist;
          //flag bikers
          concatenate LOAD
          biker as athlete,
          if(not isnull(biker),1) as biker
          resident 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?
            John Witherspoon

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