Alternative to LOOKUP with non-composite hierarchies

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.

Accepted answers

All comments

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

1 07022020.PNG 2 07022020.PNG 3 07022020.PNG

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

Quick Links

Can you help?

Unanswered questions

Request for Workflow Design: End-User Report Finalization & Stakeholder Notification with Version Co
Hi everyone, I'm looking to design a workflow and would appreciate your input or guidance on how to set this up effectively in Anaplan. So, the goal is: Once end users complete their data input, they should be able to trigger a process that: Notifies stakeholders via email with a link to a report. The report includes: A…
Generating Failure Dump - even though no warnings/error in Actions
I've few actions in our Supply model which when run takes some time on the "generating failure dump" step at the end of the action even though the import is a full success without warning or errors. This is just a few seconds when run for a single action, however we have a process with 120 actions, and most of these…
OAUTH 2.0 vs Google Sheets Extension
Hi, My org is looking to explore leveraging Google Apps script to extract data out of Anaplan and update already built templates in Google Sheets. We use the Anaplan Extension currently but find it crashes often with the amount of tabs, data, etc. Has anyone used both Anaplan OAUTH 2.0 API and Goolge Apps Script to write…