Looking for text in modules

JoanZ

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

 

 

 

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.