Hi,

I am trying to merge two datasets which look as follows:

Master Data:
Year State Constituency Candidate Winner
1991 Arunachal Pradesh Arunachal East Person A 1
1991 Arunachal Pradesh Arunachal East Person B 0
1991 Arunachal Pradesh Arunachal East Person C 0
1991 Arunachal Pradesh Arunachal East Person D 0
1991 Arunachal Pradesh Arunachal West Person E 1
1991 Arunachal Pradesh Arunachal West Person F 0
1991 Arunachal Pradesh Arunachal West Person G 0
Year: are not in order and can have different gaps in between e.g. 1991, 1992, 1996, 1998, 2004

Using Data:
State Constituency_no. Constituency District
Arunachal Pradesh 1 Arunachal East East Siang
Arunachal Pradesh 1 Arunachal East Lohit
Arunachal Pradesh 1 Arunachal East Tirap
Arunachal Pradesh 2 Arunachal West East Kameng
Arunachal Pradesh 2 Arunachal West Lower Subansiri
Arunachal Pradesh 2 Arunachal West Tawang
Arunachal Pradesh 2 Arunachal West Upper Subansiri
Arunachal Pradesh 2 Arunachal West West Kameng
Arunachal Pradesh 2 Arunachal West West Siang
State is the biggest unit followed by Constituency and then District. In few exceptions, district can be the same size or sometimes bigger than constituency.

Result wanted: District wise winner
Year State Constituency District Candidate Winner
1991 Arunachal Pradesh Arunachal East East Siang Person A 1
1991 Arunachal Pradesh Arunachal East East Siang Person B 0
1991 Arunachal Pradesh Arunachal East East Siang Person C 0
1991 Arunachal Pradesh Arunachal East East Siang Person D 0
1991 Arunachal Pradesh Arunachal East Lohit Person A 1
1991 Arunachal Pradesh Arunachal East Lohit Person B 0
1991 Arunachal Pradesh Arunachal East Lohit Person C 0
1991 Arunachal Pradesh Arunachal East Lohit Person D 0
1991 Arunachal Pradesh Arunachal East Tirap Person A 1
1991 Arunachal Pradesh Arunachal East Tirap Person B 0
1991 Arunachal Pradesh Arunachal East Tirap Person C 0
1991 Arunachal Pradesh Arunachal East Tirap Person D 0
1991 Arunachal Pradesh Arunachal West East Kameng Person E 1
1991 Arunachal Pradesh Arunachal West East Kameng Person F 0
1991 Arunachal Pradesh Arunachal West East Kameng Person G 0
1991 Arunachal Pradesh Arunachal West Lower Subansiri Person E 1
1991 Arunachal Pradesh Arunachal West Lower Subansiri Person F 0
1991 Arunachal Pradesh Arunachal West Lower Subansiri Person G 0
and so on....
I have tried a m:m state constituency merge but it chooses few districts only and does not give me unique district-winner result.

I think the problem is that am trying to merge higher order units (state and constituency) to match lower order units (district-winner) but I am unable to find a way around it.

Any help would be really appreciated.

Thank you
Purnima

P.S. I am new to the forum so in case I have missed any detail, please let me know.