How to create formula that calculates the first child of a given parent?
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