Search if a list member exists in a text line item

luizasampaio
luizasampaio
in Modeling

Hello!

 

I got a Invoices Data Load Module that gets its data from a numbered list "Invoices". The line items (invoice number, client, comment, amount, date etc) are the "Invoices" list properties.

 

We started selling a new product last month, but our ERP doesn't fill the "client" field in the invoice when it has that product code. So, all the new product's invoices got the client property blank in Anaplan.

 

But in the comments property (text format) it has the client name!! For example: "Invoice 00000, product X, quantity 1, price per unity $30, CLIENT, purchase date, delivery date".

I also have a clients name that contains all our clients names. 

 

Is it possible to create a line item in the Invoices Data Load that finds in the "comment" line item if there is a client from the client list?

Is it possible to make something that works that way?

 

Thank you very much for your attention!!!

Tagged:

Answers

  • JaredDolich
    JaredDolich

    @luizasampaio 

    Good use case. To start any analysis on text I would HIGHLY suggest moving those list properties to a system module. Much easier to do calculations from a module than a list, plus its the Planual best practice. 

    But enough about that, let's solve this one. Some reactions.

    1. Without a doubt the easiest solution and most accurate will be to get the ERP (the source) fixed so the client is populated. It's worth the investment. All other options will eventually lead to problems since the text field can vary over time.
    2. If the comment string is consistently formatted, meaning you can identify the client by parsing the text, you can use any number of text string functions to obtain the client (in text) then use FINDITEM to identify the client.
    3. Realistically, the only other option that doesn't include creating a calculation module (#4) is to create a dimensionless line item that allows the planner to select a client and have Anaplan search for all the comments that contain that client in the comments. Then apply a filter to a grid and show only those invoices.
    4. Last resort would be to create a calculation module that tests every client to every invoice. Not recommended unless you have Polaris since the sparsity will be huge.

    Sorry, no great options but from a long game strategy, I would go with #1.

    Hope that helps.

  • Misbah
    Misbah

    @luizasampaio 

     

    This is what @JaredDolich means in point 2. For this to work you need to have separators between each parameter. In our case it is comma but it could be anything like pipe, colon etc

     

    Misbah_0-1665040664155.png

    Misbah_1-1665040684299.png

    Thanks,

    Misbah

    Miz Logix

  • MarkWarren
    MarkWarren
    Or if you can extract out the Comments to a new file, these could be imported to new line items instead of doing the text calculations. The comment is already in a CSV format...
  • ShubhamCh
    ShubhamCh

    @luizasampaio 

     

    I believe @Misbah 's solution should work if you have a regular formatting in all the comments.

Categories