How Can I Automatically Identify and Delete Childless Hierarchy Items?

Occasional Contributor

How Can I Automatically Identify and Delete Childless Hierarchy Items?

Hello,

I have a constantly shifting hierarchy in place which consists of 8 levels, level 1 (top) through level 8 (bottom).  Within this hierarchy it's necessary to delete members of L7 when they have no L8 members which roll up to them.  This scenario plays out regularly as L8 members are often realigned to roll up under different parents.

 

My Question:  Is there some way to automate the identification and deletion of L7 members which don't have children?  If I could run an action to clean the hierarchy it'd save me a meaningful amount of time pruning my hierarchy.

3 REPLIES 3
Master Anaplanner/Community Boss

Re: How Can I Automatically Identify and Delete Childless Hierarchy Items?

@red11 

You can try adding a line item to your Level 8 system module that has a "1" in the formula. Also make sure there is a line item formatted as list for your level 7.

In your Level 7 system module you create a sum function to add up the 1's. If the value is greater than 0 then FALSE, else TRUE. 

Use the TRUE value boolean in the delete list action.

Addendum:

  • Always create an audit saved view that will show you what is going to be deleted before you run the action.
  • Good practice to test your process before deleting anything.
  • If you make a mistake, you'll have to roll back the model by using the history section

Jared Dolich
Certified Master Anaplanner

Re: How Can I Automatically Identify and Delete Childless Hierarchy Items?

Hello 

I have an Idea how to do it.

1) Create a SYS module (SYS01 | L8 calculation) for L8 hierarchy as a dimension. Create here Line item Count with formula = 1.

This lineitem will count your L8 items and will rollup summaries for L7

2) Create SYS module (SYS02 | L7 delete) for L7 Hierarchy as a dimension.

Create lineitem To be Deleted? as a boolean and place a formula SYS01 | L8 calculation.Count =0

3) Create new action "Delete from a list using selection..." with Name "Clean L7"

 

Hope it will help.

Regards, Nikolay

Master Anaplanner/Community Boss

Re: How Can I Automatically Identify and Delete Childless Hierarchy Items?

@red11 

 

My solution is very similar to what @nikolay_denisov suggested, and this works very well.

 

My hierarchy has 9 levels, so just subtract one for yours

 

Create a SYS module on your L8 list (again, mine is L9)

 

2020-12-18_13-04-58.png

 

Create views, for each level, using these booleans as filters:

2020-12-18_13-07-36.png

 

 

Create a Process that builds the hierarchy and then deletes the members no longer needed:

2020-12-18_13-10-03.png

 

 

Now, if you want to automate the entire process, then you will also have to delete parent members that are now detail members.

 

2020-12-18_13-17-13.png

 

Put all of these into a Process and it will be automatic.

 

2020-12-18_13-18-26.png

 

Let me know if you have questions.

 

Rob