How to create formula that calculates the first child of a given parent?

paolovm
paolovm
in Modeling

Hi,

 

I need to map high level data (Product) to leaf level (SKU) for exporting planning information to transactional systems. 

 

Each product has multiple SKUs so we'd like to automate that mapping by using the first SKU within the hierarchy. I can calculate the SKUs sequence within each product using RANK function but I'm not sure how to use this information on a module where I only have the Product level.

 

In summary this is what I have at the moment:

Source Module: SKU Level

Source Line Item: I have a RANK showing that a given SKU is the 1st in that parent (or 2nd, 3rd etc...)

 

This is what I want to achieve

Target Module: Product Level

Target Line Item: First SKU of that Product

 

Can anyone please throw some light into it? Let me know if the info above is unclear

 

Regards,

Paolo

Tagged:

Best Answer

  • paolovm
    paolovm
    Answer ✓

    Solution found by a colleague:

     

    1. Inside the target module on the target line item create a subsidiary view and add the SKU level

    2. apply the following formula IF 'L4_SKU'.Rank = 1 THEN ITEM('L4_SKU') ELSE BLANK

    3. apply for that line item the summary type "First Non Blank"

    4. Each parent will then by default derive the value of the first leaf item

     

     

    PS. The IF is not necessary for first children. The summary of the line item is what is actually doing the trick, but this if still might be useful if you want to get the 2nd or some other arbitrary member

     

Answers

Categories