How can I 'sum' non numeric fields to pull data through from one module to another?


I am trying to pull non numeric data through from one module to another. I have found that "If your mapping table contains the Source List and maps to a Target List-formatted Line Item or List-formatted Property, then use SUM in your formula." but how can I pull this information through when the data I need is text formatted and not numerical? 


I don't want to build a huge re-staging module unless absolutely necessary....

Best Answer

  • DavidSmith


    It’s not widely known, but you can use different aggregation methods for ‘summing’ on non-numeric source line items.


    Here is a solution (based on the L1 training model)

    The use case here is that going forward the business wants to model by position rather than the individual employee, so they need to map the current employee data into the generic position.


    In the source module (dimensioned by E2 Employee#) we have the following:



    The required fields and their formats are:

    • Name: Text
    • Start Date: Date
    • Current Salary: Number
    • Promotion Review?: Boolean
    • Review Period: Time (Months)

    Note, the Position ID List line item is formatted as a list – Position ID.


    The target module is dimensioned by position ID:



    The blueprint is:


     And the formulae:

    • Current Employee: 'SYS08 Employee Details'.Name[FIRSTNONBLANK: 'SYS08 Employee Details'.Position ID List]
    • Start Date: 'SYS08 Employee Details'.Start Date[FIRSTNONBLANK: 'SYS08 Employee Details'.Position ID List]
    • Current Salary: 'SYS08 Employee Details'.Current Salary[SUM: 'SYS08 Employee Details'.Position ID List]
    • Promotion Review?: 'SYS08 Employee Details'.Promotion Review?[ANY: 'SYS08 Employee Details'.Position ID List]
    • Review Period: 'SYS08 Employee Details'.Review Period[FIRSTNONBLANK: 'SYS08 Employee Details'.Position ID List]

    Obviously, you do need to be careful with FIRST and LASTNONBLANK if there are duplicates that you are effectively trying to “sum”, but depending on the use case, these, and the other methods are a very powerful way to achieve a simple solution to the problem.


    See here for more details of the different options


    I hope this helps




  • **** thank you so much David this is amazing! 


    This will solve so many headaches, thanks for the very comprehensive reply.

  • I would add that TEXTLIST is also an option.  It's not applicable for this example.


    But as we know TEXTLIST is a very heavy calc and should be avoided if possible (as per Planual 2.02-11).  If you do need to use it, be careful and ensure there is no other way to solve the problem, and make sure the target dimensions are appropriate to reduce the target calculation size