LOOKUP Problem!!!

logikalyan

Hello community,

I am going through something with Lookup formula as below.

I have a module (say module1) dimensionalised by Account list with Account code column. I also have a List (say list1) with account code, employee code and names. In the module 1, I have created line items for employee code and name. For which I am using a lookup as below

List 1.employee code[LOOKUP: module1.Account] in the target module 1. I am not seeing any error but the column is empty even with formula.

Its got to be something very silly.

Can anyone advise please?

Regards

Kal

devrathahuja

Hi @logikalyan ,
Is the code for your list1 also same as account code or is it a list created based on combination of properties?

If the code isn't the same as the account code but you have a property, then create a SYS module with that list & use finditem on account list to have a mapping between the account list & list 1.

You can then fetch the values using firstnonblank on this new line item.

You're currently not seeing any error as it is just pulling the top level value.

logikalyan

Hi Devrathahuja,

Thanks for the quick response.

List1 is created based on combination of properties as it has one to many relationship between account and employees.. And the account code is same in the module1 and list1.

Thanks

Kal

devrathahuja

@logikalyan - Yes, then use a find item to map the list1 to the account list in module 1 & then either use firstnonblank on this property to create a reverse mapping & lookup using that or directly use firstnonblank to pull the value.

Equivalent to your list1 with property as employee code etc-

Equivalent to your module 1 with account-

Hope this helps! :)

logikalyan

Hi devratha

Do I have t0 create a code in List1 as a mapping between employee and accounts and then use that code to get employee id and name in the module?

Sorry if I am confusing?

Kal

devrathahuja

@logikalyan - No, you already have an account code in list 1 as a property right? And also, that'd be the code for the account list?

logikalyan

@devrathahuja

Yes both account code and employee codes are properties of the list. Employees are assigned to multiple accounts which is the relationship. I now have a module with same account code but not the employees. I thought a simple lookup should work for this but not. Don't understand why Anaplan thinks the formula is correct but there is no result.

Kal

devrathahuja

@logikalyan - Because for lookup you need a 1:1 mapping between the members.
It accepts the formula because the syntax is correct, however there exists no mapping between the account & employee so it returns no value.

You need to create the mapping using finditem on the account code.

Refer to the above screenshots, the trucks list ~ Accounts in your case.
SYS Plants ~ the other list with all the properties.

Use finditem to establish a mapping between the 2 & then use firstnonblank to get the correct values.

Both the screenshots also contain the formulas for your reference.

logikalyan

@devrathahuja

I might be confusing you, for which I apologies. Below is an illustration of what I am trying to do with screenshots.

1. I have a list Test A with accounts A, B, C, D, and E.

TESTA

2. I also have a list Test L same accounts (A,B,C,D,E) with properties Employee code and name as below. This is the relationship. You can see employee A is assigned to three different accounts.

TESTL

3. Now I have a module with Employee Code and Names as line items and Test A as dimension.

So can I get data for employee code and name in the module from TestL using TestA list items (A,B,C,D,E) on lookup.

Hope this is clear. If not I will figure out a way to do this as I do not want to bother you anymore as you have been already very helpful.

Thanks

Kal

devrathahuja

@logikalyan - No worries! :)

Create a SYS module with dimension Test L & create a new line item X = finditem(test A, name(item(Test L))

Next, in the module with Test A where you want employee names - use the formula testL.employee name[firstnonblank: sys module.x]

The finditem above helps you create a mapping between your test L & test A lists, since you have the same accounts, you can use finditem & create a mapping.

Now you can fetch any data using the firstnonblank function instead of a lookup, it some what acts as a reverse lookup in this case.

logikalyan

@devrathahuja

Thank you.

I will try this tomorrow and let you know.

Kal

logikalyan

@devrathahuja

The objective is to get employee names from TESTL list in a module where we have Accounts (A,B,C,D, &E).

As per your advice, I have created a system module with line item X (I formatted this as list A, is that right) and added formula FINDITEM(TEST A, NAME(ITEM(Test L))). This resulted in exactly A, B, C, D, & E. However, we need A, B, A, C, A right due to one to many relationship.

Next in the module with testA, I have created a line item Y (Is it text format or Test L List format) and added formula testL.employee name[firstnonblank: sys module.x] and I get below error.

Where am I going wrong?

Kal

devrathahuja

@logikalyan - Yes, that is right.

It is text.

Formula "TestL.employee name" is supposed to be this-

So use the exact name of the list that you have in the model & then .employee name (So list.property format)

logikalyan

@devrathahuja

Thank you so much for quick responses.

This works in a module with TestL as dimension. However, my requirement is to get this data in a module with TestA as dimension which has the same accounts but not employee code and name.

So I need a formula for employee code and name in TestA module referring to module with TestL list.

Hope its clear

Kal

devrathahuja

@logikalyan - The formula will be in module with dimension Test A.

It'd refer the testl list (Refer the exact name of the list in the formula above instead of testl).

logikalyan

@devrathahuja

Everything is referred correctly but it still does not work. Doesn't matter, I will look carefully to see where its going wrong or if its actually possible.

Thanks again for your responses

Kal

devrathahuja

@logikalyan - Correlate your example to the screenshots provided above. Trucks & Plants. You have 2 lists as well & want to move data from one to another.

The error you shared is because you're referring to "testl.employee code" in formula and it isn't recognized as any member in the model (Cause of a mismatch in names).

Could you paste a screenshot of the exact list with the name of the list visible?

logikalyan

@devrathahuja

Below is TestL list with Employee code and name as properties and all data populated:

TestA list with same accounts:

Module with TestA as a list and employee code and name as line items:

I want the line items in the module with TestA and employee code and name line items populated using the relationship in TestL list (first screenshot). Whats common in both is first column account.

Sorry for being a pain on a Friday.

Kal

devrathahuja

@logikalyan - With the name of the list shown in the screenshot :) Since that is where the issue is!

logikalyan

@devrathahuja

Its amazing how you are responding to my questions. Even my users dont respond like this😃

I did not quite get it. I am referring directly to the list so there wont be a problem with the list name.

What formula do you think Employee code and name in module with listA should have. Lookup is not working for this.

Kal

devrathahuja

@logikalyan- Thanks :)

The only issue I see here is that the formula needs the correct list name. A small space or a missing ' will also give you the error you shared earlier.

Source Test List L with the mapping line item (Test A Mapping is List A formatted)

Test List L that has the property of employee code-

Target module with Test A dimension & employee code pulled in using the formula-

I hope you can now relate it easier to your example & replicate it for your case.

More importantly, the focus should be here to understand what we're doing, which is using firstnonblank as a reverse lookup to pull the relevant data. This would be useful for future in a lot of use cases as well :)

logikalyan

@devrathahuja

For the first illustration from your response I am using formula in Test A module. Please correct me if I am wrong. This throws below error.

FINDITEM(Test L.Employee Code, TEST A)

Where should this formula be written TestL module or TestA module to get the mapping. And also mine is not 1-1 relationshio, its 1-many.

Kal

devrathahuja

@logikalyan - Formula will be written in a SYS Test L module.

Formula will be what you had earlier-
"FINDITEM(TEST A, NAME(ITEM(Test L))). This resulted in exactly A, B, C, D, & E. However, we need A, B, A, C, A right due to one to many relationship."

You need A B C D E here because you're mapping the 2 lists Test L & Test A which both have members A B C D E.

Till this step, there is no mention of employee code. We're just establishing the mapping.

Employee code comes in the next step where we refer to that & the mapping created above to pull the value.

logikalyan

@devrathahuja

Yes that find item formula is resulting in exactly A,B,C,D,E. My formula and screenshot is below:

This mapping feels wrong and. not sure how this is useful. So you are saying we do need this mapping for my requirement?

Kal

devrathahuja

@logikalyan - Yes. It is the correct mapping. You are mapping 2 lists, list A & list L. They both have members named A through E right? That's why in the mapping you need it A through E & not the employee code.

Employee code is the next step.

The firstnonblank: ***** here the **** is going to be this new line item you just created.

logikalyan

@devrathahuja

This is working perfectly and thank you so much.

Sorry it took long for my dumb brain to understand this.

Regards

Kalyan

devrathahuja

@logikalyan - No worries :) This way of mapping & pulling data across would surely be helpful in other use cases as well eventually!

logikalyan

@devrathahuja

I am asking this question to you as it is very similar.

I have employees that are assigned to different clients. Their bonuses are different with different clients. Employees assigned to different clients (one to many relationship). i.e. one employee can be assigned to multiple clients and their bonuses are different for different clients.

I have a module (target) where I need to calculate total bonus for each employee. I have a list defining their relationship. I also have a client details modules with employees assigned to different clients.

For total bonuses, I am referring to client details module bonuses line item summing up on employees in the relationship list. I am in same problem again. No errors but it is not summing up total bonus for each employee for multiple clients.

How do I fix this? Any advice much appreciated.

Kal

devrathahuja

@logikalyan - You need a module with employee & client dimension in the module. Pull the bonus values against it & sum it up at employee level from there using the sum summary method.

This is also assuming that multiple employees can be assigned to the same client? If not, then you could just use source[sum: employee mapping] in the destination where the employee mapping would be against client & the source would be the bonus amounts.

logikalyan

@devrathahuja

Just an illustration below to understand more as I dont want to confuse you again. OR I am confuse :-(

I have a Client list and Client model with the same list. The model has two line items 1. Bonus and 2. Employees. You can see employees work for multiple clients.

Client model with client list. Employee and Bonus as line items.

I also have a Employee list and Employee model with same list. This model has line item bonus. Over here I want to get the total bonus for employees for all the clients they are working for. So for Employee 1 it should give 900.

Employee model with Employee list. Bonus line item.

I am using Client Model.Bonus[sum: Client Model.Employee] formula and I get multiple errors.

How can I pull bonus into employee model by referring to bonus in client model and summing up in employees.

Once again really thanks for all your advises and appreciate it a lot.

Kal