Conditional Parenting

edited December 2022 in Modeling

Hi all,


I've got this situation where my region hierarchy is dependent not just on country but by the entity under which a sale is made. The table below is how I'd like the parenting to work. Region L3 entries then map on a 1:1 basis to Region L2 and again to L1. The entity specific region may roll up along with the geographical ones to roll up together.


EntityCountry (Region L4)Region L3
Entity 1FranceEntity specific Region
Entity 1ChinaEntity specific Region
Entity 2FranceEurope
Entity 2ChinaAsia
Entity 3FranceEurope
Entity 3ChinaAsia


At the moment I've got a mapping module doing the work for me. However, this means my Region L4 list is separate to my other 3 levels in terms of parent-child relationship. This solution also produces a significant amount of sparsity. If anyone has a trick, I'd love to hear it. Thanks in advance.