Text to Column feature in Anaplan

Sachinsourav02

Anaplanners,

Is there a way we can convert a text to column like how we do in excel. Example below:
You can see Column A (member name) and Column B (Projects Involved/textlist). Using Texts to Columns we can convert column B dynamically to different columns. As its dynamic some members have 3 projects while some may have more. Can this be achieved in Anaplan ? any tips and suggestions would be appreciated

rob_marshall

@Sachinsourav02

This one of the reasons why TextList() should not be used, aside from the performance issues it may cause. Without knowing the full architecture and knowing exactly what the desired final outcome, there are a couple of different ways. I would assume you have a hierarchy with members rolling up to Projects and this how the above was achieved. Do you have a Projects Flat list as well as an Employees Flat list? If so, you can create a module with both flat lists in the Applies To and then use filters to list all projects that the employee is apart of.

Sachinsourav02

@rob_marshall

Appreicate your response !

1) Members do not roll up to projects, rather members are assigned to projects using line items. The TEXTLIST is achieved through a different process using some calculation involving member scores for each project, that is if score is assigned then consider the employee else not.
2) Both members and projects are list however not hierarchically connected hence the module option with filter may not work.

rob_marshall

@Sachinsourav02

Precisely, they don't have to be in a hierarchy connected, it is called a cartesian join where you make the connection, in your case Employee's assigned to Projects and then using a filter to only show valid intersections.

Take a look at this post where I modelled it the correct way in order to not use TextList(). Hopefully this helps.

Sachinsourav02

@rob_marshall

I'm unable to see the link to the post, would you be able to re-share it ? thank you

rob_marshall

@Sachinsourav02

Yep, my bad…

Lookup, Sum and Textlist Cheat Sheet

AlejandroGomez

Oct 14, 2019

Hi all,

 

During the last CPX in London, which was fun and insightful, came to my attention the fact that still now-a-days the most searched topic in Anaplan Community is "how to use Lookup and Sum".

 

That is why I decided to share with you all this visual and brief explanation of how Lookup, Sum and Textlist work, I hope you find it as handy as I do!

 

This post is intended to be complementary to the existing explanations in Anapedia,  a "cheat sheet" to be revisited quickly when you get doubts about how these formulas work or you are not sure which one to use to make the data flow into your module. 

 

 To use this Cheat Sheet you only need to bear these 3 points in mind:

1. Think in terms of Source Module (where our data currently is) and Target Module (when do you want to retrieve it)

2. Be aware of the dimensionality of both modules (Source and Target) 

3. Be aware of the format of the Line Items being used. Either because some functions only work with data in a specific format (SUM with Numbers and TEXTLIST with Text) or because you will need LI formatted as the Lists being used as Dimensions in Source/Target modules. 

 

CHEAT SHEET

LOOKUP

Target Module must have a LI formatted as a List ("LI: Employee" in the example below) being used as a Dimension in Source Module ("Dim Employee"). 

 

 

TEXTLIST

Even though this formula is meant to retrieve text strings, it can be used as a Reverse Lookup. The only limitations is that, whereas Lookup can retrieve any kind of data formats, Textlist only retrieves Text-formatted values.

 

So  turn your source data into text, get them into your Target Module and convert them back to their original format. 

SUM

It works quite similarly to TEXTLIST but only works with Number-formatted Line Items. For instance, in the example above, since Salary is by definition a Number-formatted magnitude, we should have used SUM. 

 

Remember that SUM is to be used when there is no hierarchical relation between the Dimensions in Source and Target Modules.

 

• If one of the Dimensions in Target is Parent of the Dimension in Source, aggregation happens automatically (as long as Summary Method is not set to "None" in the Source Line Item)
• If one of the Dimensions in Target is Descendant of the Dimension in Source, then LOOKUP should be used. 

I hope this helps you to connect your data across modules rapidly next time you hesitate about how to do it! 

 

Kind regards,

 

Alex.

---

View Post