How to do Excel Search in Anaplan

Hi Anaplanners

Has anyone built an Excel Search function equivalent in Anaplan? What I would like to replicate is:

1. Client has a text mapping table (screenshot 1) which maps Strings in Column B to their expense classification (Column C & D)

2. SAP extraction which contains a column called Text (screenshot 2) , Column H.

3. Then client planner will search the Text in screenshot 1 Column B in SAP extraction Column H. If any match is found, expense classifications will be mapped following the mapping table. Otherwise they all classified as "Others"

Screenshot 2022-07-13 at 1.42.04 PM.png Screenshot 2022-07-13 at 1.43.02 PM.png

Any idea is welcome to share. Thanks!

Tingting

Accepted answers

All comments

ankit_cheeni

Hi @tingtingxia

I will give it a try. How are you hoping to load the data in Anaplan? Can your 'Strings' column in Anaplan be loaded as a list?

If Yes, then you can do a simple FINDITEM (FINDITEM(Strings List, Text in H))in Screenshot 2 which will give you a Strings match from Screenshot 1. The other columns can then be pulled in using a LOOKUP function on the FINDITEM column.

tingtingxia

Hi Ankit

Thanks for the prompt reply. But the tricky situation here is, the SAP Text column is not exactly the same as String column in mapping table. The rule is, as long as SAP text column CONTAINS the string, then the expense type will be mapped.

Tingting

ankit_cheeni

@tingtingxia

Uff! I cannot think of a lean solution. What can be done is a mapping module with Strings as one dimension and SAP Extract as another - this allows you to use FIND function to find the name of Strings within the name of SAP Extract.

My concern is this will be a really bloated module and might take up a lot of space.

tingtingxia

Hi Ankit

Thanks for the idea! Let me experiment with this method and I will keep you posted.

Tingting

rob_marshall

@tingtingxia

Can you not use the Find() function for the string?

Rob

tingtingxia

Hi @rob_marshall

Would you elaborate a bit more on your recommendations?

Thanks!

Tingting

rob_marshall

@tingtingxia

I will ping you in slack

tingtingxia

Thanks @rob_marshall but I am not on Slack. You can drop me an Email if you prefer.

Tingting

tingtingxia

Hi Ankit

I am experimenting with your idea and yes, I managed to do the Excel "Search" function with a module dimensioned by Strings and SAP Text (and yes, it blew up the model size as expected). Screenshot 2022-07-14 at 1.45.25 PM.png

My next step is that if the text position is not zero, then I need to bring the Parent(String)/ Parent(Parent(String) (in the screenshot case is CPF Mobile) to SAP data load. So if I go back my SAP loading module (flat module dimensioned on numbered list which is equivalent to rows in source file), refer back to this line with text column as "05/04 S10.....", the Expense type will be populated as CPF Mobile. (Hierarchy: String rolls up to CPF Mobile Accessories rolls up to CPF Mobile).

Screenshot 2022-07-14 at 1.47.44 PM.png

I am a bit stuck in turning this around. Any input will be appreciated.

Tingting

rob_marshall

@tingtingxia

Yes, you are correct, I thought you were an internal employee because there was an A next to your name.

First thing, it looks like you have Time as part of key, maybe not actually, but your "text" column as Time as part of which will increase the size of your list and cause performance issues. If this is the case, you need to create a truly unique key for your data where your transactional data (data that changes across time periods) gets loaded to a transactional module (dimensionalized by Time) and then the metadata (data about the unique key) gets loaded into a SYS module.

https://community.anaplan.com/t5/Best-Practices/Data-hubs-purpose-and-peak-performance/ta-p/48866

Secondly, I would create a list of the strings you are looking for. For example, if you are looking for:

The problem with the above is how many strings are you looking for as this will impact the following statements?

Then I would create that as a list. Create a module with those values with the mapping list member associated with that member.

Now, for the text string, you can use the find function, something like this:

Find("abc",text string)>0 which should be formatted as a boolean.
this is the **** part, you can create a nasty IF then else with a bunch of Finditems(), but there really needs to be another/better way.
once you get the text that you are looking for into a list formatted line item, then you can do a lookup to the mapping module.

Hope this gets you on your way.

tingtingxia

Hi @rob_marshall

Yes and thanks for the ideas. I have replied to Ankit in the previous post whereby I managed to do the find text process. Now I am a bit stuck at turning around the lookup thing. Any idea would be greatly appreciated!

Screenshot 2022-07-18 at 11.10.33 AM.png

Tingting

Quick Links

Can you help?

Unanswered questions

How have you from-scratch built lot-level, geo-based Inventory Consumption?
We’re working on modeling inventory consumption at the lot level, accounting for shelf life considerations, since we need visibility into which lots may not consume—both for risk reporting and E&O analysis. Each item may have customers with different shelf life requirements. For example, item A may need 270 days for some…
How to bring Row data into Columns?
Hi, Can you help me, how can we get row data into columns using Unique ID? Per attached below first snapshot, I want to bring rountingOrder 4, RecipientName, Sent and Completed dates in 1 row as show in sanpshot 2. Do I need to create Hierarchy list and Map?
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…