The objective is to compare values in a TEXT formatted line item and identify the values that occur more than once (duplicates).
Sneakerz is an up and coming shoe company with a variety of shoe brands in their brand portfolio. They are expanding internationally after having started from their humble origins in countryside Australia. As part of their international strategy, the team has decided to consolidate their brands.
Sneakerz’ has an employee who is an avid spokesperson of Anaplan and its Connected Planning capabilities. He has convinced the team to invest in
As part of the implementation, there is data coming in from a source system around the number of sales across each of the above brands (brand name is in text format).
The marketing team wants to review the sub-brands at Sneakerz, remove the sub-brand and create a single power-brand that is unique. Anaplan should highlight the duplicates in this new brand name.
For e.g. ‘Comfort’ is a brand with ‘Gen X’ and ‘Y2K’ as the sub-brands.
We need to isolate ‘Comfort’ from the text and highlight duplicates of ‘Comfort’ in the landing module.
1. Create a data staging module “DAT01 – Brand Sales” that holds the incoming data in a numbered list.
2. Create line items as follows:
a. Delimiter Start – Find the position of the “-“delimiter in the text.
b. New Brand Name – Isolate the text in the “Brand” line item using the position of the delimiter and trimming any whitespaces in the result.
3. Create a new line item ‘Not First Occurrence | New Brand Name’. This will check that the New Brand Name is unique across the numbered list (and highlight if there is more than one occurrence). The formula for the line item is as follows:
Note: The formula above ignores the blanks (if any) in the New Brand Name column.
4. Add a line item ‘Cond Format | Not Unique?’ to indicate second or more occurrences in ‘New Brand Name’ line item using conditional formatting.
5. Go out of the Blueprint view. The module should look like follows:
6. Optional: Use the Conditional Formatting feature to highlight the duplicate occurrences in the New Brand Name field.
The final output is shown below:
The final output view indicates that there are three New Brand Names that are duplicates namely – Comfort, Ethical, Futura. On the other hand, two New Brand Names – Runner and Bambino are unique in the data set.
Pitfalls of this Approach
The limitation with this solution is that you can only identify the successive duplicates and cannot tag the first occurrence as a duplicate value too. The alternative solution highlighted below mitigates this but is highly performance intensive (therefore NOT ANAPLAN RECOMMENDED).
Always follow best practices and turn off summary calculations if not necessary.
Rename and organize modules in relevant functional areas.
Avoid Daisy Chaining in the calculations if they can run in parallel.