Register

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

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.

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
Moderator

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

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

Master Anaplanner/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
Moderator

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

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

Master Anaplanner of the Year

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

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 ID Specialized Sales Assistance 0060b00000s6vWSAAY 102869, 104301, 102653, 102418, 105541, 101860, 106476
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.

Moderator

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

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

Master Anaplanner of the Year

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

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.

Moderator

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

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

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?