Generate Unique Codes for List Imports with Long Text Values

Question

Hi ALL,

I'm using Product Category, Product Line, and Budget Type to generate a unique Code for a list.  The display string is generated as:  Product Category & "_" & Budget Type & "_" & Product Line. The Code is currently generated using:  LEFT(Display String, 60)

The issue is that Product Category and Product Line contains long text values. As a result, different combinations are truncated to the same 60-character code, causing duplicate codes and the import error: More than one value processed.

I also tried changing the order of concatenation to:

Product Line & "_" & Product Category & "_" & Budget Type

However, this didn't resolve the issue because the code is still being truncated.

When I increased the code length to:  LEFT(Display String, 100)

The duplicate code issue was reduced, but additional list items were created that were not expected.

What is the recommended approach for generating a unique code from these three text fields when the combined string can exceed the desired code length? Any best practices for creating a stable, unique code without introducing duplicate or unnecessary list items?

Please advice!

JeffreyG · Answer

Hi @Isha, as an alternative you could also consider using HASH functionality upstream to create a unique code in the source data - basing the HASH on the attributes you are using to define unique members. Many tools, including Anaplan's ADO, have HASH() functions that can be leveraged for this.

However, just be weary of using names to define unique keys (regardless of if you are using HASH or manual text concatenation). If the name ever changes, but still is intended to represent the same product line/category/etc, it will generate a net new key as the unicity is based on name alone. As Gaurav notes, defining codes for the upstream components of what you are looking to create a unique key off of will help prevent this too.

SinduraDappili · Answer

Hi @gaurav_sharma-bng

Thank you for your response!

Unfortunately, we don't have codes associated with these items in the source system. Also, in the current model, I couldn't find any existing unique fields that could be used to generate these combinations using codes from other attributes.

In this situation, Is there a recommended approach for defining unique codes?

gaurav_sharma-bng · Answer

Hi @Isha,

Instead of using the full member names to generate the unique code, it would be better to use the predefined codes for each individual list item.

Example:

* Insurance Category = INS
* Budget Type = BUD
* Product Line (Private Medical Health Insurance) = MED

Rather than generating a code such as:

Insurance Category_Budget_Private Medical Health Insurance

the resulting code should be:

INS_BUD_MED

If these are already maintained as separate lists in Anaplan, you can assign a unique code to each list member and use those codes to construct the derived list code.

Alternatively, the concatenated code can be generated in the source system before the data is loaded into Anaplan.

Regards,

Gaurav