Altering Line Item Formula with lookup

Highlighted
New Contributor

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!

9 REPLIES 9
Highlighted
Certified Master Anaplanner

Re: Altering Line Item Formula with lookup

Hello @jmroesser 

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. 

Einas
"Give a Man a Fish, and You Feed Him for a Day. Teach a Man To Fish, and You Feed Him for a Lifetime"
Highlighted
New Contributor

Re: Altering Line Item Formula with lookup

Hello @einas.ibrahim!

Here is the screenshot:

jmroesser_0-1603384263919.png

Please let me know if you need more information.

 

Highlighted
Certified Master Anaplanner

Re: Altering Line Item Formula with lookup

Hello @jmroesser 

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
"Give a Man a Fish, and You Feed Him for a Day. Teach a Man To Fish, and You Feed Him for a Lifetime"
Highlighted
New Contributor

Re: Altering Line Item Formula with lookup

@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]

Highlighted
New Contributor

Re: Altering Line Item Formula with lookup

@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?

Highlighted
Certified Master Anaplanner

Re: Altering Line Item Formula with lookup

Hello @jmroesser 

 

Let me explain the concept again because it is usually confusing to beginners (and some not so beginners 🙂)

This is the step by step logical thinking one should use to arrive at the formula. Of course, once you understand the concepts you would automatically do all this in one step.

Q: What are we trying to accomplish? 
A:
Find out the Bonus % for a particular employee in the Target Module Emp02 ( a Target Module is where we are writing the formula)

Q: Where can we find the Bonus % information/values?
A: In Emp03. This is the Source Module since it contains the data we are looking for.

Q: What information do we need to provide to the Source module -Emp03- to return a single value? in other words, what is Emp 03 dimensioned by?

A: Emp03 is dimensioned by E1 Department and Role.
This basically means if we give Emp03 a Department and a Role we will get the Bonus % for this Department and Role.

Q: Do we have this information required by Emp03 (Department, Role) in our Target Module Emp02 to be able to query Emp03 and return the Bonus % for a particular employee? (the data we are looking for?)
A: Short answer is NO, The department and role data are not included in Emp02. This means we can't query Emp03 directly.

Q: What do we do then?
A: We find an intermediary module that we can query using the information we have in Emp02 - #E2 in this case- to return the information I need - department and role.

Q: Where can I find the employee department and role?

A: In SYS08. This is our Mapping Module.

Q: What do we do now?

A: We query the Source (Emp03) for the Bonus % by giving it the department and role information it requires. The way to provide this information to Emp03 is to use an intermediary SYS08 to give us department and role which in turn we pass to Emp03

 

The way we construct all this in a formula would be something like this:

Target Module. Line Item = Source Module. LineItem[LOOKUP: mapping module.data 1 required by Source Module, LOOKUP: mapping module. data 2 required by Source Module, LOOKUP: mapping module.data 3 .....and so on ]

 

Notice that Country didn't come up which means we don't need it.
Please read the above steps carefully and let me know if I need to clarify anything.
After you digest the information, write the formula, test it, and send it to me to make sure you got everything right.

 

image.png

Einas
"Give a Man a Fish, and You Feed Him for a Day. Teach a Man To Fish, and You Feed Him for a Lifetime"
Highlighted
New Contributor

Re: Altering Line Item Formula with lookup

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?

Highlighted
Certified Master Anaplanner

Re: Altering Line Item Formula with lookup

@jmroesser 

 

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

Einas
"Give a Man a Fish, and You Feed Him for a Day. Teach a Man To Fish, and You Feed Him for a Lifetime"
Highlighted
New Contributor

Re: Altering Line Item Formula with lookup

I just did! Thanks again!