topic Re: Finding relationship rating in App Development

Finding relationship rating

Kishor_Thorat — Sat, 16 Nov 2024 17:58:38 GMT

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_ID	Program_Name
1	ABC
2	CDE
3	XYZ

Entity Involved:

Program_ID	Entity_Name	Entity Country
1	AB	FR
1	CD	BE
1	ED	GB
1	EF	FR
1	MN	BE
2	AB	FR
2	CD	BE
3	CD	BE
3	ED	GB
3	LM	GB

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.

Re: Finding relationship rating

sunny_talwar — Tue, 13 Oct 2020 15:58:46 GMT

@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?

Re: Finding relationship rating

Kishor_Thorat — Wed, 14 Oct 2020 07:50:02 GMT

@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.

Re: Finding relationship rating

sunny_talwar — Wed, 14 Oct 2020 11:15:48 GMT

@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

Re: Finding relationship rating

Kishor_Thorat — Wed, 14 Oct 2020 12:03:22 GMT

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