Alternative to LOOKUP with non-composite hierarchies

ChrisAHeathcote

I have a list in which there are parent and child relationships defined. Ideally, these should have been created within a composite hierarchy but at this stage I am not considering redesigning the lists. 

 

Is there a function whereby I can reference one of these parent items? 

 

Example - Shoreditch, Westminster, Clapham are parents of London 

 

 

 

LOOKUP does not work as it only operates on composite hierarchies.

JaredDolich

@ChrisAHeathcote 

 

FINDITEM should do the trick

Then you can use PARENT on it.

 

CommunityMember115881

Hi Chris,
Can you explain a little bit more what you are doing, not sure I follow. If you could attach a couple of print screens of your lists/modules that would help a lot. Thx

ChrisAHeathcote

I have tried this approach using ITEM to create a mapping....this is not my issue.
The problem comes from the fact that I can not use the parent column to lookup the parent. In this example it is London.
LOOKUP will not return the value of London when used for child list items as it is not part of a composite hierarchy. This is because London is defined as a parent of the child items within the same list and is not defined as a parent within a composite hierarchy.

JaredDolich

@ChrisAHeathcote 

Got it.

You'll want to read @DavidSmith's best practice post on ragged lists.

Rather than me reproducing it here, the summary is that you can flatten the list so you can utilize the Item / Parent.

https://community.anaplan.com/t5/Best-Practices/Production-lists-and-ragged-hierarchies-logic/ta-p/33591

 

@DavidSmith also has some suggestions on how to treat Parents. It's an amazing article. Let us know if that is closer to what you needed.

Or, as @CommunityMember115881 suggested, give us a little more detail on what you're trying to do.

My hunch is that you just need to use a system module rather than use a list. You can even populate the system module with the parent from the ragged list.

 

Anxious to hear more.

 

ChrisAHeathcote

I get what @DavidSmith is suggesting but it seems a rather elaborate solution to quite a simple looking problem. It seems like there should be a simpler solution to hand.

In terms of what I am trying to achieve it is a % calculation of the line / total for the list. The current solution uses SELECT but we are trying to remove these to allow the model to be more flexible and sustainable.

CommunityMember115881

I'm sorry but I still have no idea of what it is you're trying to achieve. If you attach some print screens I'd be more than happy to help.

 

Amaya

 

I suppose I could see what bothers you.

Perhaps, it occurs like below with one list item that has a hierarchy in it.

 

Once I tried to solve this with some tricks, but after all, I got to know regularized hierarchy is the best.

 

Sorry, I don't have a good answer.
I am watching how this discussion goes, too.

 

Regards,
Taichi.

CommunityMember115881

Thanks Amaya for the explanation.

 

So I've managed to solve it using Time for sequential calculation.

Warning, it aint pretty but it does work.

The idea is simply to SUM then LOOKUP using one list member per parent. 

If you look at my print screens, the result is simply summing the values as per the last column. Then LOOKUP on it.

The trick is to automatically get one list member per parent.

Let me know if you need more explanation for each line item calculation.

Cheers

 

 

karank_*4578

I am with you on this as i have experienced similar issue before but i resorted to switching to heirarcies as everything else was either too elaborate or too workaroundy .
good luck

ChrisAHeathcote

Hi All, 

 

I appreciate all your efforts to help. The solutions provided while appearing to offer a solution are all too lengthy and cumbersome where this can be resolved by redesigning the lists and removing the list subtotals. 

As this is a client model in which I have not been the principle model builder this will be my suggestion to the model team. 

 

Thanks