Qlik Community

Ask a Question

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Finding relationship rating

Hello Experts,

If someone can give lead to below problem.

I want to calculate Collaboration rating between countries based on below data.

Collaboration rating = m * n ( m entities of x country  * n entities of y country involved in same program)

Program:

Program_IDProgram_Name
1ABC
2CDE
3XYZ

 

Entity Involved:

Program_IDEntity_NameEntity Country
1ABFR
1CDBE
1EDGB
1EFFR
1MNBE
2ABFR
2CDBE
3CDBE
3EDGB
3LMGB

 

Output

Relationship ( x-y or y - x)Rating
FR - BE(3*3) = 9
FR - GB(1*1)= 1
BE - GB(1*2) = 2

 

Edit: First column of Entity Involved table is Program_ID, I mistakenly spelled to Project_Id.

4 Replies

@Kishor_Thorat Not sure how your two sample tables linked to each other and hence don't understand the  reason for the expected output. Would you be able to explain what is going on?

Contributor II
Contributor II

@sunny_talwarThank you for your response. Indeed there was mistake on column name, I corrected it.

What I want is to find which countries are involved the most.

@Kishor_Thorat Okay, so I understand that the link between the two tables is Program ID, but how exactly are you getting 3 * 3 for FR - BE... may be walk us through this example so that we can understand what you are really after

Contributor II
Contributor II

Yes sure, let me try to explain.

Formula is:

Collaboration rating = m * n ( m entities of x country * n entities of y country involved in same program)

For example, 

For country FR we have two entities AB (participating in program 1 and 2), EF (participating in program 2)

For country BE we have two entities CD (participating in program 1,2 and 3),  MN (participating in program 1)

If we take intersection of both countries, in common they have their entities involved in programs 1 and 2.  BE has entity CD is involved program 3 but no FR entity is involved in program 3 hence program 3 will not be considered for rating between FR and BE.

Lets consider FR as x country and it has entities AB involved in 2 programs and EF involved in 1 program

m= 3 (2*AB + 1*EF)

Consider BE as y country and it has entities CD involved 2 programs and MN involved 1 program.

n = 3 (2*CD + 1*MN). 

hence rating = m * n = 3*3 = 9

Similarly, FR and GB each has only one entity involved on 1 common program_id 1.

hence rating = m * n = (1* AB) * (1* ED)  = 1* 1 = 1