Need to remove duplicated text values from a text Line Item
I have an requirement of identifying unique values of customer in each department. Please see below module which is have a dimension of departments list with three list items(D1,D,2,D3) and a line item(Customer Names) which is having concatenated names of all customers in each department with delimiter as ",". I want to remove duplicate names in each department and produce an output of unique names in each department in a sperate line item(Unique Customer Names). Please see sample data arranged in below table.
|L2: Unique Customer Names(Text)||ABC,XYZ||ABC,XYZ||PQR|
Please check this formula in Anapedia: FIND
1) I assume you already have a created list of Customers in Anaplan (if no - you should create it)
Let's take your example.
Department D1 D2 D3 L1:Customer Names(Text) ABC,XYZ,ABC ABC,XYZ PQR,PQR L2: Unique Customer Names(Text) ABC,XYZ ABC,XYZ PQR
2) Then you need a module dimensioned by Customers and Departments.
3) In this module you will search the names of customers in each Department. Use FIND formula for it. The output of the formula is a place in the text where it is found.Let's take your example.
4) Let's create first line item called "Find first occurrence". Here we will search the first occurence of all customers using formula:
FIND(<customer name>, 'L1:Customer Names(Text)')
Find 1st occurrence D1 D2 D3 ABC 1 1 0 XYZ 5 5 0 PQR 0 0 1
5) Another line item - text - will be based on the first one and be named "Customer for Department".
IF Find first occurrence = 0 THEN BLANK ELSE <customer name>
Customer for Department D1 D2 D3 ABC ABC ABC XYZ XYZ XYZ PQR PQR
6) Now all we need is to get everything together to see what each department has as Unique Customer Names.
This can be resolved by using TEXTLIST formula but i would not recommend you to use it as the Planual says to avoid it if it's possible. This function may lead to performance issues in the model. Please ask yourself if you really need to get all results concatenated together in one cell?
The workaround may be to pivot our Customer for Department line item and filter each combination by nonblank values to get the same list of elements. The output may look something like this:
ABC XYZ D2 ABC XYZ D3 PQR
Let me know if you need any other explanation.1
Thanks Kirill for your response.
I have customer name as another list, In reality I have already three lists as dimensions for line item 1: Customer Name. If i bring in customer name list as fourth dimension into new line item or module then it is causing maximum size limit of a line item 2 billion cells. Let me see how can i reduce my line item size.
I would appreciate if you have any other solution with the same existing dimensions on line item 1.
1) Are you sure all three list dimensions are necessary to store/calculate this inital data with customer names? I am asking because sometimes we have extra dimensions from the module but such metadata like Customer name may not need that many and some of them (like time scale, for example) can be removed from the line item.
2) It depends on how you store this data now. Is it originated from a flat file? If so then you could use the flat file lines as one dimension and the customers as the other one. Then you can use the approach I expalined before.
Anyway, I think I need more info on how did you get this concatenated values in the first place.0