Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
crpacheco
Contributor
Contributor

Count of a different combination of dimensions

I have two simple tables:

Recruits (The school that recruits signed with):

IdRecruitChoice
1Florida State
2Miami
3Florida
4Alabama
5Miami

Offers (The school that offered a scholarship to a recruit):

IdRecruitOffer
1Florida State
1Miami
1Florida
1Alabama
1Georgia
2Miami
2Alabama
3Florida
3Miami
4Alabama
4Florida State
4Florida
4Miami
5Miami
5Florida
5Florida State
5Alabama

I'd like to have a report with the following result:

Florida StateMiamiFloridaAlabamaGeorgia
Florida State1-11-01-11-0
Miami1-11-12-1
Florida0-11-10-1
Alabama1-11-11-0
Georgia0-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.

1 Reply
ogautier62
Specialist II
Specialist II

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