Looking for text in modules

Hi, I need some orientation on this:

I need to look for a text in the column "P2 product Brand" (first photo) in another module (second photo) and if it is there, I need to return TRUE otherwise False this formula should populate the last column of the first photo. Both columns are formatted as lis, and they are populated using the same list.

I've tried using the lookup function, but it returns an error mismatching common dimension, I also tried "finditem" function but it returns a list and I need a boolean. Any Idea on how should I approach my formula? Thanks

Accepted answers

All comments

medaganeshamanikanta

Hi @JoanZ

You can't use lookup function as there is no common dimension between source and mapping criteria.

You can use a FINDITEM function but syntax for FINDITEM function is FINDITEM(List or Time, Item to find) where the data type for the "List or Time" argument is list or time dimension, and the data type for the "Item to find" argument is Text.

In your case when you have used the FINDITEM function both arguments are list formatted. That's the reason it is showing the error.

What I thought is you can create another lineitem named "P2 Product Brand Text" and format it as text and formulate the linetem with NAME function whose syntax is NAME(List item).In your case List item argument will be P2 Product Brand. It will convert the list into text.
Now for the linetem First occurrence of the representative brandfamily fomulate using below formula
ISNOTBLANK(FINDITEM('SYS03 P1 Product Brand Family Details'.Representative Item, P2 Product Brand Text).
This will find for the text present in the P2 Product Brand Text Line item in the Representative Item Line item in the SYS03 module and returns the list item. Now Isnotblank function returns true if there is a listitem.
Try this and let me know whether it's working or not.

JoanZ

Hi @medaganeshamanikanta thank you, I tried your suggestions for creating a new line item formatted as text and then using it as the second input within the finditem function but now it is telling that Finditem function should take a hierarchy as argument. Any ideas here?

medaganeshamanikanta

Hi @JoanZ

Have you checked the format of Representative Item. Is it list formatted with P2 Product Brand?

JoanZ

Yes @medaganeshamanikanta , It is list formatted with P2 Product Brand, I don't know if the fact that both line items (used in finditem function) are using the same list is impacting somehow.

medaganeshamanikanta

Yes I also tried from my side.Same thing is coming to me also.
What I understand now is for FINDITEM function sytax , first argument is List or Time

https://help.anaplan.com/0668e215-a0d2-4ad1-b93f-3c2a56a9f5c2-FINDITEM

We should use only lists dimension in that argument. We should not use Lineitem formatted with list. That's the reason it is showing that Representative is not found as a heirarchy in the model.

I think we have to approach the problem in some other way.
Let's see if any one comments on this question.

Inacornejo

Hi @JoanZ,

You should add another column to SYS06 with the Representative( list P2), so you can compare if P2 is equal to Representative. IF 'P2 Product Brand' = Representative THEN TRUE ELSE FALSE. This is would be for your line item: First Occurrence of Representative Brand Family.

Representative Brand Family line item should use IF First Occurrence of Representative Brand Family THEN 'P1 Product Brand Family' ELSE BLANK.

medaganeshamanikanta

Hi @JoanZ

As @Inacornejo was mentioning create a line item named Representive Item in SYS06 P4 Product Brand Size Material Details module and format it with list P2 Product Brand. Then use the formula 'SYS03 P1 Product Brand Family Brand Details'.Representative Item[LOOKUP: 'P1 Product Brand Family]. This will bring the representative items into SYS06 Module.

Now for line item First occurence of Representative brand family use the formula IF P2 Product Brand=Representative Item THEN TRUE ELSE FALSE.
Hope this helps you.

JoanZ

Thank you @Inacornejo and @medaganeshamanikanta the approach given by Ina implies not bringing the Representative from SYS03 which is fine at this point, trying to bring it from SYS03 definitely needs a different approach because is not possible with a lookup because they don't share common dimensions. I created a line item with P2 list and then the boolean with the comparison and it works, thank you both.

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…