Error when updating hierarchies with list items

fredrickstraube
fredrickstraube
in Integrations and extensibility

I'm trying to update a list hierarchy from a module, which I have updated with an import file, and everything seems to be working just fine, that is, my list items are updated. However, since I have the same list item occurring several times in my import file, and also in the module I import from, I get the error message "Another row has already been processed with this key" and then the yellow warning triangle. 

 

Does anyone know how to avoid this? I don't want my client to think that there is an error in the import when it actually worked just fine. 

 

Thanks a lot!

Tagged:

Best Answer

  • rob_marshall
    rob_marshall
    Answer ✓

    @fredrickstraube ,

     

    The IsFirstOccurrence() function works on the list within the source module, not the target list.  For example, you have a transaction module using Trans List as your list, but you want to find the first occurrence of a line item (Product) to be used in several lists downstream.  The formula would be isfirstoccurrence(Product, Trans List) which can be used as a filter for your other views.

     

    Does this help?

     

    Rob

Answers

  • alexpavel
    alexpavel

    Hi, 

     

    You can create a boolean line item and use FIRSTOCCURRENCE function to identify only the distinct elements and codes to update the list. 

    Create a saved view with the filter based on the boolean line item and use it as a source for updating the list. 

     

    Hope it helps!

     

  • fredrickstraube
    fredrickstraube

    Hi Alex,

     

    Thanks a lot for your reply. However, I'm not sure if this would work for me, since I am trying to update 5 lists from one module, and as far as I understand, ISFIRSTOCCURRENCE can only be used when you have the list you want to update as dimension. Or am I wrong?

     

    Thanks!

  • alexpavel
    alexpavel
    Hi
    Yes, indeed firstoccurrence function works for 1 dim at the time.
    This is why you will need to create a boolean and a saved view for every of 5 lists that you need to update.

    Cheers
    Alex
  • rob_marshall
    rob_marshall

    @fredrickstraube ,

     

    Check out this training video (305 Hub Model Hierarchy Management)

     

    https://learning.anaplan.com/enrol/index.php?id=419

     

    Thanks,

    Rob

  • fredrickstraube
    fredrickstraube

    Thanks a lot for this, but it seems like an extremely complicated solution. Do you know if there is an easier way to do this?

  • fredrickstraube
    fredrickstraube

    Hi Rob,

    This is exactly what I needed and it works perfect!

    Thanks a lot for your help!

  • svbhagat
    svbhagat

    Hello @rob_marshall ,

    Thank you for this solution.  I'm trying to use this logic but I have an extra layer that I would like to review.  

     

    I have a System Module in my Data Hub Model where I am bringing in all Hierarchy levels as well as Boolean Line Items for the Subsets I would like to build in my list.

    My System module is built off of a Full Account Flat List.  I've created a line item for the AU Number from that.  This AU Number shows up multiple times due to the different Accounts it is associated with.  I then have Boolean Line Items to select based on the Accounts.  

     

    I'm trying to build a list for the AU and have Subsets that should be populated from the Boolean Line Items in the System Module.  But because the List gets populated based on the first occurrence of that AU, the Boolean for the subsets are not getting populated correctly.  

    What additional logic could be added to resolve this issue?

     

    Here is a screenshot:

    System Module:  As you can see the Salary and Other Expense are Account lines for the same department.  The Printing Boolean is only selected for the Printing Account.  But because Salary is the first occurrence, the Printing Boolean is not getting populated when I create the list subset.

    svbhagat_1-1612993203764.png

    List:

    svbhagat_2-1612993268441.png

    Thanks

  • rob_marshall
    rob_marshall

    @svbhagat 

     

    Ok, yes, yours is a bit more complex, but still very doable.  Is that 750 (the AU number) a list or text formatted?  If it is not a list, I would create a flat list in your data hub of just the first occurrences of that AU Number and then create a line item that is formatted as AU Flat (so a Finditem(AU Flat, AU Number) - name the line item AU Flat List) in that module (the one you were showing, the transactional module).

     

    Then, you can create a SYS module based off the newly created list (AU Flat) for the subsets formatted as Boolean.  For for each of the new subset line items, you can use the following formulas:

    • Admin ss: Source Module.Admin ss[ANY:Source Module.AU Flat List]
    • Acqu ss: Source Module.Acqu ss[ANY:Source Module.AU Flat List]
    • Deferred ss: Source Module.Deferred ss[ANY:Source Module.AU Flat List]
    • Allocated ss: Source Module.Allocated ss[ANY:Source Module.AU Flat List]
    • Controlled ss: Source Module.Controlled ss[ANY:Source Module.AU Flat List]
    • Printing Admin ss: Source Module.Printing Admin ss[ANY:Source Module.AU Flat List]

    Now, from this SYS AU Flat module, create your views to populate the spoke model.

     

    Let me know how it goes.

     

    Rob

  • svbhagat
    svbhagat

    @rob_marshall 

    Hello Rob,

    Thank  you.  That worked.  

    Here is the new system modules I'm building for the AU Hierarchy:  FQA SYS07 Exp Mgmt L3-AU.

    svbhagat_2-1613078225770.png

     

    I would also like to use the new System module created above to build out the AU hierarchy.  I'm trying to now bring in the Parent (AU L2) of that AU Number Flat from the original System Module (FQA SYS01) but running into the following error:  

    svbhagat_1-1613078034960.png

     

  • rob_marshall
    rob_marshall

    @svbhagat 

     

    Did you follow the video in course 305?  I would do it that way where it is doing a lookup on a flat module where the parent of that member is defined.

     

    Rob

  • svbhagat
    svbhagat

    @rob_marshall 

    Thanks for that reminder.  I was using our current Flat List and we didn't have properties setup in our current flat list so I had forgotten about that.
    I went back and created the Properties for the attributes and am now able to bring that into the System module.

     

    Thanks for your help.

  • svbhagat
    svbhagat

    @rob_marshall 

     

    Hello Rob,

    I have the following two questions that are related as well.

     

    1) We are in the process of updating the Codes for our lists.  If the list is going to be part of a hierarchy list, should the code include the Parent Code in front of it with a hyphen or can we leave it just as a single code?  If including the Parent code, could you help me understand why?  We may add new L3 in the future and are trying to figure out a best way to maintain the process of creating a code so would prefer having a single code vs. including the parent in the code as well.   Our preference is Option B but want to do Option A if that is the best practice and we understand why.

     

    Please see example below:

    Option A:  

    svbhagat_1-1614797198606.png

    Option B:

    svbhagat_2-1614797406823.png

     

    2) List Properties:  I understand Flat list should not have properties and it should only have a Parent code so that it can be used to generate a hierarchy list.  What is the best way to bring in a property line item from the Data System Module into the List System Module to capture the property?  

  • rob_marshall
    rob_marshall

    @svbhagat 

     

    Ok, let'**** these in order (by the way, good questions):

    • Should you append or prefix the code with the parent.  In a flat list (call it Cost Centers), no for the very reason you stated.  Now, in the composite hierarchy, the answer would be a yes especially if you are using a numbered list so you know where you are in the list.  In the 305 video, I believe I have a flat list of cost centers and then I have a SYS module where I am importing the parent.

      2021-03-03_13-55-19.png
      Now, when I build my composite list, I can use the code of the parent with a delimiter concatenated with the code of the member.

    • Really, you shouldn't be using properties in a list period, except if they fall into one of these exceptions (Planual Rule 1.05-03) and Display Name for a numbered list.  There is one other exception that @jasonblinn found, but since that is on the Old UX, I will refrain.  So, this should be your order of operations:
      • Load the list (in this case Cost Center Flat)
      • Load the properties or metadata to a. SYS module
      • Create a module, using the Cost Center Flat list), to derive your composite hierarchy (this is where the 305 video comes in).  

    Does that help or clear things up?

     

    Rob

  • alexpavel
    alexpavel

    @svbhagat Names and Codes in composite lists need to be unique. This is the reason why sometimes you need to concatenate the parent code to the detail code of the Parent to the Details.

    If the list is a numbered list, the request for uniqueness is applying exclusively to the "Codes" and not to the "Names". 

     

    Another, easier way to make unique the Parent Code from Details codes is to concatenate to the Parent and/or Details the level. 

    Example: Parent Code: L1_1001  Details code: L2_1001. 

     

    This is mandatory if you update the lists via an import action. 

     

    Strange enough: If you update manually a list, Anaplan accepts the same Code or Name in the Details list identical to the Parent....but not via automation through an import action.

     

    If both parent and details codes are numeric values, there is a chance that the parent code would be present also in the details codes and you will receive an error when you update the lists. 

     

    From your example, it seems that Parent codes are alphanumeric (letters) and Details codes are numeric (only numbers)... In this particular case I do not see the reason why you should concatenate in the Details Codes also the Parent Code. 

     

    The same reasoning is applying also for the Name (Descriptions): the same Description can be present under different parents  ( for example descriptions like "Unknown" or "Other" can be present under different Product Families/Accounts). Usually, the way to make the Names(Descriptions) unique is to concatenate to the Description the Code. 

     

    Hope it helps.

    Alex

  • alexpavel
    alexpavel

    @svbhagat  Just came into my mind: The hierarchy can change in time... one element of details can change its parent at some point. 

     

    Usually, the expected behavior is just to change the parent of the detailed element and all the historical data will be automatically restated based on the current new hierarchy. 

     

    If the Parent Code would be present as concatenation in the Details code, the default behavior in these cases would be that a new element will be created in the hierarchy and the same Details code will appear twice:  once under the Old parent concatenated with Old Parent code and secondly under the New Parent concatenated with New Parent code. 

     

    If the Details Code has only concatenated the Level to make it unique then automatically with an import action, the Details Code will be updated with the New Parent and the element moved from the hierarchy of the Old Parent. 

     

    However, if the desired outcome is to maintain the historical data under the Old Parent and only all new data to be updated under the New Parent then concatenating the Parent code to Details code is the solution to achieve this.

     

    Hope it helps

    Alex

Categories