How to get the nth occurrence of an item using the same list

DavideVitale
DavideVitale
in Modeling

Dear Community,

 

Hope you can help me with this requirement, this is the scenario: there is a list of products and a dedicated module that allows the user to select the replacement product from the same list.

A product can be replaced by one product only, while any product can replace multiple existing products.

 

Referring to the attached image, do you know how to populate the module on the right? Specifically, I would like Anaplan to return which substituting products replace the related existing products, based on the first module on the left (1st occurrence, 2nd occurrence, nth occurrence, etc.). This module is dimensioned by an additional list of 'n' items (Generic Products).

Would it be feasible to get this result through some calculations and additional line items? Do you suggest any other method?

 

Picture.png

Thank you

Davide

Tagged:

Best Answer

  • JaredDolich
    JaredDolich
    Answer ✓

    @DavideVitale 

    Great use case. Happens all the time in Retail. We sometimes refer to this scenario as a recursive join.

    There are a couple of ways you might approach this but it really depends on the use case. Can you share a little bit more on how you intend to use this?

    Some ideas:

    1. Did you consider creating a structured hierarchy? Since an item can only have one replacement, then you have a natural parent-child relationship. You can use filters to only show those items that have replacements (the parent, as shown in your second chart).
    2. Separate the lists (2 flat lists) so you have separate dimensions, one for rows, one for columns. Again use filters to only show items that are used as replacements.
    3. Use the SUM function on the list formatted line item of the items that have replacements. The Target module would use a list, or list subset, of the items that have replacements.

     

Answers

  • AlejandroGomez
    AlejandroGomez

    Hi @DavideVitale 

     

    Just in case it helps (and please keep in mind that Text calculations may be ending up being a burden for the performance of the model, as per stated in the Planual)

     

    You can use the following approach:

     

    On the first Module you need 3 LI:

    1. formatted as products for selecting the replacement - manual input
    2. formatted as product to retrieve each of the items of the list - ITEM(Products)
    3. formatted as text to turn each product item into text - NAME(Line Item 2)

    AlejandroGomez_0-1611877414152.png

    AlejandroGomez_1-1611877433828.png

     

    In the second module:

    1. Just need one line item formatted as text with the TEXTLIST function as shown below:

    AlejandroGomez_2-1611877557797.png

     

    Also, in addition to this and to the answer from @JaredDolich , I suggest that you have a look at this post's answers: https://community.anaplan.com/t5/Anaplan-Platform/Lookup-Sum-and-Textlist-Cheat-Sheet/td-p/53085

    not because I posted it but because @DavidSmith  and @rob_marshall made very valuable comments that you might find useful.


    Cheers!

    Alex

     

  • DavideVitale
    DavideVitale

    Hi @JaredDolich and @AlejandroGomez, many thanks for your valuable feedback!

    We are still reviewing the requirement and I think we will use 2 separate flat lists.

     

    Cheers

    Davide

Categories