Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have two simple tables:
Recruits (The school that recruits signed with):
IdRecruit | Choice |
---|---|
1 | Florida State |
2 | Miami |
3 | Florida |
4 | Alabama |
5 | Miami |
Offers (The school that offered a scholarship to a recruit):
IdRecruit | Offer |
---|---|
1 | Florida State |
1 | Miami |
1 | Florida |
1 | Alabama |
1 | Georgia |
2 | Miami |
2 | Alabama |
3 | Florida |
3 | Miami |
4 | Alabama |
4 | Florida State |
4 | Florida |
4 | Miami |
5 | Miami |
5 | Florida |
5 | Florida State |
5 | Alabama |
I'd like to have a report with the following result:
Florida State | Miami | Florida | Alabama | Georgia | |
---|---|---|---|---|---|
Florida State | 1-1 | 1-0 | 1-1 | 1-0 | |
Miami | 1-1 | 1-1 | 2-1 | ||
Florida | 0-1 | 1-1 | 0-1 | ||
Alabama | 1-1 | 1-1 | 1-0 | ||
Georgia | 0-1 |
The result shows a comparison of the schools on the left versus the school on the top.
FSU won 1 recruit over UM and lost one recruit to UM (FSU and UM offered to two coincidental recruits, and each signed one)
UM won 2 recruits over Ala and lost one to them (UM and Ala offered to three coincidental recruits and UM signed two of them, and Ala signed one)
The number of the right is easy, assuming my dimension on the left is Choice and my dimension on the top is Offer then it's simply count(Offer)
The number on the right, I have no idea how to do. Probably something like count(total <Offer = Choice> Offer), but I'm not sure if that works because I couldn't try it because I can't get the syntax right.
Hi
first modify your script like this :
outer join(offer) load IdRecruit,Offer as Offer2 resident offer;
offerchoice:
load *, 1 resident offer where Offer <> Offer2;
drop table offer;
so you have a new table with all crossproduct :
then you can create a pivot table with Offer and Offer2 as dimension,
where expression is :
text(sum(if(Choice=Offer,1)) ) & '-' & sum(if(Choice = Offer2,1))
regards