Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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
Athletes:
//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.
If I understood the question, -sum(Swimmer=Runner and Runner=Biker). See attached.