Get Unique Values Using Source[TEXTLIST: Mapping]

Highlighted
Master Anaplanner of the Year

Get Unique Values Using Source[TEXTLIST: Mapping]

"Textlist" is one of my favorite formulas when trying to reduce sparsity in calculations. However, Source[TEXTLIST: Mapping] becomes ineffective because there isn't the option to rollup unique values. For anyone who ran into the same challenge before, hope this post can be helpful.

Workaround:

    • Use rank function to stage the data set before using “Source[TEXTLIST: Mapping]”

Example Below:

Data Set: The list of support tickets by opportunities. Each support ticket was assigned to a person.

End Goal: Calculate the connected revenue for each employee who worked on the support ticket for the opportunities. Revenue amount is available at opportunity level.

SerenaZ_0-1591663625941.png

Steps: To get to the end goal without bringing in extra dimensions, I used the textlist formula to show the people who worked on the opportunity. The issue with directly using the “Assigned to EEID” field is that there are duplicated values. I don’t want to credit the same person multiple times for the same opportunity, so I need to stage the data to show just the unique value. 

Step 1) - turn the text field into a numeric field. Assigned to EEID_Number = Value(Assigned to EEID) 

Step 2) - using rank formula Assigned to EEID_Rank Seq = TEXT(RANK(Assigned to EEID_Number, ASCENDING, SEQUENTIAL, Assigned To EEID <> BLANK, Oppty_List)). Text() formula is optional, just my personal preference.

Step 3) - using another rank formula Assigned to EEID_Rank Min =TEXT(RANK(Assigned to EEID_Number, ASCENDING, MINIMUM, Assigned To EEID <> BLANK, Oppty_List)). Again Text() formula is optional, just my personal preference.

Step 4) - Leave only unique values Assigned to EEID_Unique = IF Assigned to EEID_Rank Seq = Assigned to EEID_Rank Min THEN Assigned To EEID ELSE BLANK


After completing these 4 steps, I was able to use Assigned to EEID_Unique to show the unique employees who worked on the opportunity using Assigned To EEID_Unique[TEXTLIST: Oppty_List] and credit each of them once with the opportunity amount.

12 REPLIES 12
Highlighted
Master Anaplanner/Community Boss

Re: Get Unique Values Using Source[TEXTLIST: Mapping]

@SerenaZ 

 

First of all congratulations on being Master Anaplanner of the Year:)Please do not use TEXTLIST at all. It is the worst performing function out there and it breaches Planual Rule 2.02-11. As my mentor @rob_marshall  says it is brutal.

 

In your case I think you can use 

Assigned To EEID_Unique[FIRSTNONBLANK: Oppty_List] or 

Assigned To EEID_Unique[LASTNONBLANK: Oppty_List]

 

Let me know your thoughts on this

Misbah

 

Edit: Also if you wanted to see only Unique Values  ISFIRSTOCCURRENCE would have done the trick

Highlighted
Community Boss

Re: Get Unique Values Using Source[TEXTLIST: Mapping]

TEXTLIST is heavy but sometimes un-avoidable, as stated in the planual.

 

I've never thought of this function to reduce sparsity though.


Nathan Rudman, Anaplan Model Builder
Highlighted
Master Anaplanner/Community Boss

Re: Get Unique Values Using Source[TEXTLIST: Mapping]

@nathan_rudman 

 

I concur. Sometimes it is unavoidable to proceed without TEXTLIST but in this case it is avoidable.

Highlighted
Master Anaplanner of the Year

Re: Get Unique Values Using Source[TEXTLIST: Mapping]

@nathan_rudman 

 

To elaborate on the sparsity benefit I mentioned. Here is how I typically go about it. 

In places where I see one to many relationships, like the one below where one opportunity can be associated with a handful of people. I use textlist to aggregate up the unique values ("Specialist Sales Assistance" column) and then parse out each person. In the example below, there will be 7 columns, each column has a unique ID. Then I use the parsed data for calculation instead of nesting the people dimension with thousands of records. 

Oppty IDSpecialized Sales Assistance
0060b00000s6vWSAAY102869, 104301, 102653, 102418, 105541, 101860, 106476
Highlighted
Master Anaplanner of the Year

Re: Get Unique Values Using Source[TEXTLIST: Mapping]

@Misbah Thanks for taking the time to read through the post! I am aware that Textlist is not an ideal function to use, however, FIRSTNONBLANK AND LASTNONBLANK is not going to work for this problem. If for every opportunity there is only one person assigned, then there is no issue with your solution. However, it is guaranteed that an opportunity will be touched by more than one person. I need the list of all the individuals who worked on it and get credit for the effort. FIRSTNONBLANK AND LASTNONBLANK will only pick the first person or the last and ignore everyone else. Same goes for ISFIRSTOCCURRENCE. Hope this helps clarify. 

 

Textlist is one of the oldest formulas out there and it is really unavoidable as @nathan_rudman mentioned in these types of scenarios, I do think it is a good comprise to make to avoid sparsity knowing it might slow down model performance a bit and also has the10,000 characters limit.

 

 

Highlighted
Master Anaplanner/Community Boss

Re: Get Unique Values Using Source[TEXTLIST: Mapping]

@SerenaZ ,

 

Serena - would IsFirstOccurrence(), in your instance, work if you concatenated the opportunity with the employee ID?  Using TextList() is, as @Misbah stated, absolutely brutal on performance and memory consumption.  And yes, textlist() is one of the oldest functions we have, but might in fact be the worst, from a performance standpoint.

 

Rob

Highlighted
Master Anaplanner of the Year

Re: Get Unique Values Using Source[TEXTLIST: Mapping]

@rob_marshall 

Hi Rob - good to see you here! 

I can see how IsFirstOccurrence() can replace the rank() formulas to get to the unique value, but I don't think it can replace textlist.

Highlighted
Master Anaplanner/Community Boss

Re: Get Unique Values Using Source[TEXTLIST: Mapping]

@SerenaZ ,

 

Agree on not replacing the textlist(), but it could be an avenue in replacing textlist() by using ANY as the ANY needs to have a boolean.

 

Rob

Highlighted
Master Anaplanner of the Year

Re: Get Unique Values Using Source[TEXTLIST: Mapping]

@rob_marshall  I have done the ANY approach too, but will that cause unnecessary data sparsity though, if combining the 2 dimensions will create a module that doubles the size of a model?