How to Use Lookups from Multiple Modules with Different Dimensions

Isha
Isha
in Modeling

I have three modules with different dimensions:

  1. Product Details:
    • Dimension: 'Product'
    • Line Item: 'Dispatched Qty'
    • Formula I used is : 'Order Import Data'. Dispatched Qty [LOOKUP: 'Order Import Data'. FIND_PRODUCT]
  2. Attributes Allocation:
    • Dimension: 'Allocation ID'
    • Line Item: 'Dispatched Qty'
    • Formula is : 'Order Import Data'. Dispatched Qty[LOOKUP: 'Order Import Data'. FIND_ALLOCATION]
  3. Record Key:
    • Dimension: 'Issue Key'
    • This module should look up the 'Dispatched Qty' from both the Product Details and Attributes Allocation modules.

Currently, I’m using this formula to retrieve data from Attributes Allocation:

Attributes Allocation. Dispatched Qty [LOOKUP: 'Allocation']

However, I also need to include the 'Dispatched Qty' from the Product Details module in the Record Key module's 'Dispatched Qty'. Since Product Details and Attributes Allocation have different dimensions, I’m unable to use a single LOOKUP on the same line item due to dimension incompatibility.

How can I configure the Record Key module to effectively look up data from both modules? Please advise!

😀

Answers

  • Dikshant
    Dikshant

    Do you have text-formatted product line items in the Record Key module? If yes, then create another line item (FIND PRODUCT) and formula FINDITEM(Product list, Product ID). then final formulla will be

    Attributes Allocation. Dispatched Qty [LOOKUP: 'Allocation'] + Product Details.Dispatched Qty[LOOKUP: FIND PRODUCT]

  • Isha
    Isha
    edited December 1

    Hi Dikshant

    Thank you for the suggestion!
    I've Product line item in the Record Key Module.

    I have a question, The formula consolidates Dispatched Qty values from the Attributes Allocation and Product Details modules based on the respective mappings (Allocation and FIND PRODUCT) for each Issue Key.

    Attributes Allocation Module:

    Allocation ID

    Dispatched Qty

    A1

    100

    A2

    150

    Product Details Module:

    Product

    Dispatched Qty

    P1

    200

    P2

    300

    Target Module ( RAM Key)

    Issue Key

    Allocation

    FIND PRODUCT

    IK1

    A1

    P1

    IK2

    A2

    P2

    Final Result in RAM Key Module:

    Issue Key

    Dispatched Qty (from Attributes Allocation)

    Dispatched Qty (from Product Details)

    Total Dispatched Qty

    IK1

    100

    200

    300

    IK2

    150

    300

    450

    Dispatched Qty is 100+ 300 for IK1.

    Dispatched Qty is 100+ 300 for IK1. Is this what our formula does? please advice?

    😀

  • TristanS
    TristanS

    @Isha I'm a bit confused with the formulas you provided. From what I can tell the formulas for items 1 and 2 shouldn't even work. But you say that's how it has been setup? Can you please advise on the following:

    1. What are the dimensions for the module "Order Import Data".
    2. What is the line item format for FIND_PRODUCT in "Order Import Data"
    3. What is the line item format for FIND_ALLOCATION in "Order Import Data"

Categories