Need to remove duplicated text values from a text Line Item

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. 

 

DepartmentD1D2D3
L1:Customer Names(Text)ABC,XYZ,ABCABC,XYZPQR,PQR
L2: Unique Customer Names(Text)ABC,XYZABC,XYZPQR

 

 

Thanks,

Suman.

Answers

  • @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.

    DepartmentD1D2D3
    L1:Customer Names(Text)ABC,XYZ,ABCABC,XYZPQR,PQR
    L2: Unique Customer Names(Text)ABC,XYZABC,XYZPQR

    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 occurrenceD1D2D3
    ABC110
    XYZ550
    PQR001

    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 DepartmentD1D2D3
    ABCABCABC 
    XYZXYZXYZ 
    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:

    DepartmentsCustomers

    D1

    ABC
     XYZ
    D2ABC
     XYZ
    D3PQR

    Let me know if you need any other explanation.

  • 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.

     

     

  • @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.