Altering Line Item Formula with lookup

Hello fellow community users! I was wondering if anyone else had issues changing line item formula with lookup with the following error message: "format of mapping used for lookup doesn't match any dimension of the source" and what to do to remedy the situation.

Thanks in advance!

Comments

  • Hello @john.roesser 

    My guess is that you are using a lookup value -in the LOOKUP statement - that doesn't correspond to any dimension in the source target you are referencing in the same formula.

     

    If you provide an example or a screenshot, I can help you with the formula. 

  • Hello @einas.ibrahim!

    Here is the screenshot:

    jmroesser_0-1603384263919.png

    Please let me know if you need more information.

     

  • Hello @john.roesser 

    From the formula I see you are looking up the bonus from the Emp 01 module using 3 values:

    1. Department

    2. Country

    3. Role

    meaning you are passing these 3 values from the target module Emp 02 to Sys 08.
    Do you actually have Country in the Emp 02 to pass?

    Let me know if you still need hekp


       

  • @einas.ibrahim I do for country but not for role, let me reconfigure to EMP03 and see what happens. Should it look something like: IF Employed? THEN Salary * 'EMP01 Employee Drivers'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Country] ELSE 0, 'EMP03 Employee Drivers by Role'.Bonus %[LOOKUP:'SYS08 Employee Details'.Role]

  • @einas.ibrahim The IF Employed? THEN Salary * 'EMP01 Employee Drivers'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Country] ELSE 0, 'EMP03 Employee Drivers by Role'.Bonus %[LOOKUP:'SYS08 Employee Details'.Role] ELSE 0 gave a response as invalid this time. Are you seeing anything in the lookup that is not correct?

  • Wow thank you very much for the detailed description! From the information I read, I understood that the formula is :

    IF Employed? THEN Salary * 'EMP03 Employee Drivers by Role'.Bonus %[LOOKUP: 'SYS08 Employee Details'.Department, LOOKUP: 'SYS08 Employee Details'.Role] ELSE 0

     

    Because EMP03 bonus is the source module line item needed, and then LOOKUP SYS08 department and role are the mapping due to containing the necessary information, while country is no longer needed and that was deleted. I did not receive an error when I entered that into the EMP02 Bonus blueprint formula. Am I missing anything from the formula?

  • @john.roesser 

     

    Nope, you are good to go.
    If you found my response helpful, please mark it as a solution so others can find more easily.

  • I just did! Thanks again!

  • I got the same formula entered but came back with "Data mismatch: format other than LIST or Date being referenced as a relation". Please help fellow community!

  • Hi @einas.ibrahim !

    That's a great structured and clear explanation!

    Also schema with modules (about how it works) is very helpful!

    It helped me to understand much better!

     

    Thank you very much!

     

     

     

  • Thanks einsas.ibrahim for Step by step process 

     

  • Hello @einas.ibrahim

    i follow the step by step you provide and it so helpful i get the formula but when i enter/apply it the to the E2 i get an error which i attached. please help where is my mistakes? i have include those error as i try twice differentlyError formula for E2.JPGError with formula E2 department and Role,,, role is not recognize.JPG

  • Hi

     

    I have seen this in L1 also:

     

    - firstly explore the aguments required for LOOKUP: https://help.anaplan.com/f8baa402-606d-4764-a349-d8003fa383be-LOOKUP  e.g. 

    Values to lookupNumber, Boolean, date, time period, list, or text line item


    - secondly once you beleive your mappings/arguments are correct; look at what you are trying to map :

    e.g if the list you are referencing does not have a top level, this will cause the error also:

    Screenshot 2022-08-02 at 13.01.09.png

    Hope this helps.

     

    Ravinder

  • @erijustine

    Are you sure you followed the instructions step by? and that you understand the purpose of each step? Because it certainly works. Please reread the instructions step by step and ensure you understand why you are doing each step.

    The First error message you screenshot about the automatic sum is usually caused by failing to provide a list item in your LOOKUP, that the target module is expecting (because it is dimensioned by that item's list). For example, which role you need the information for. When you don't provide a particular list item for a list used as the dimension in the source module, the Anaplan Engine assumes you want the data for all the roles in the list,  That's why it looks for the Top Level as that where the aggregate information is. While adding a top level as suggested by

     

     Will probably make the error go away, it doesn't mean it will produce the correct result. It is also against Best Practice to add a top level to lists if you don't need it causes performance issues.
    Check here to understand Top Level


    A possible reason for the second error is that Bonus% has a non-alphanumeric, which means you have to single quotes around the name.


    Good Luck