FINDITEM formula gives blanks whereas I expect values

Love the formulas here. My best guess is that you're picking up a space in the parsing. You can check that by looking at the length of the Site 2 text for instance. If you're all good on the parsing then make sure the list item is in the list and spelled correctly without any special characters.

Just some ideas.

In that post I sent you, did you read down to where I explained why not to use TextList() and how to get around it? It starts with...

Rob

All comments

JaredDolich

Just some ideas.

@JaredDolich , thank you for your quick response.

You were right, looking at the length of the string revealed that it was taking into account spaces.

Change the site x (text) formulas to starting position "+2" instead of "+1" and the length from "-1" to "-2" did the trick!

Thank you for your help!

MarkWarren

"I want to summarize that information on SKU-level, so for each SKU I want to mention which sites produce it."

A better way to do this would be to have a boolean where volume > 0 in your source module. It would show all the sites for each SKU rather than limiting it to 4. Pivot the view so you have SKU's on rows and Sites as columns.

This use of textlist and subsequent text manipulation will have a large performance cost.

https://community.anaplan.com/t5/Anaplan-Platform/Lookup-Sum-and-Textlist-Cheat-Sheet/td-p/53085

I can emphasize enough of what @MarkWarren stated about the use of TextList(). TextList() is evil on performance and there is a reason why we have a rule dedicated to it (2.02-11).

Also, you might want to check out this thread on how to get around it....

Rob

Hi @MarkWarren ,

Thank you for the suggestion.
I know which SKU is produced by which sites, I have this information in a module with SKUs list as a dimension and Sites-list as a dimension.

What I want to do is to move that information into a module which only has SKUs as a dimension. So I want to bring the site into a line item. Usually a SKU is only produced in one site. There will be few exceptions where it is produced in multiple sites, then I would just need to have a boolean line item flagging that it is a multi-site sourced SKU.

I don't see how I could use the SUM and boolean trick to accomplish this...

Take a look at the post above, it explains it for you.

hi @rob_marshall ,

I took a look at that cheat sheet post, but I don't think I understand how I get around the use of textlist.

I use the textlist as is explained in the post, this gives me all sites where the SKU is produced in a text-formatted line item, for each SKU in my target module. This is what I need. I then use finditem to split the text value into multiple text values if the SKU would be produced in more than one site.

I don't know how I could use SUM or LOOKUP to do the job.

Would appreciate any help!

In that post I sent you, did you read down to where I explained why not to use TextList() and how to get around it? It starts with...

Rob

Hi Rob,

Oh, now I see, I did not read through to the bottom initially.

It took me some time to "see the light", but now I figured out! Thank you so much!

I am now using the firstnonblank to show the first site that makes the SKU. Next to that I have added a number line item in my source module and I use a SUM in my target to check how many sites produce the SKU. If that number is higher than 1, I flag that SKU as a multi-sourced SKU. Attached you can see the comparison finditem vs textlist. I am now only showing the first site and indicating if there are multiple. which is sufficient.

I will now remove the textlist and the finditems to save space 🙂

Thank you for your help!

using_firstnonblank_127608.JPG