Need to remove duplicated text values from a text Line Item

Suman Reddy

Hi Members,

 

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. 

 

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

 

 

Thanks,

Suman.

KirillKuznetsov

@Suman Reddy 

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:

Departments	Customers
D1	ABC
	XYZ
D2	ABC
	XYZ
D3	PQR

Let me know if you need any other explanation.

Suman Reddy

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.

 

Thanks,

Suman.

 

 

KirillKuznetsov

@Suman Reddy 

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.