5 Replies Latest reply: Aug 3, 2016 3:49 PM by Chris Smith RSS

    Help with using SubField to split names and $

    Ryan Minaker

      Hi there,

       

      I have a data set tracking sales similar to this, where I have multiple names in one column that I need to split out so we can track each person individually.

       

      EngagementRevenueName
      A25,000Ryan, Luka
      B10,000Sarah

       

      I've used LTrim and SubField to split the names and clean the data and it works perfectly and gives me a new table like this.


      LTrim( SubField([Name], ',')) as [Name]

       

      EngagementRevenueName
      A25,000Ryan
      A25,000Luka
      B10,000Sarah

       

      My problem is that I need to Sum the total [Revenue] regardless of who's working on what, which should be 35,000, but the number I get is 60,000. I understand why, because 25,000 is being counted twice.

       

      Can anyone offer any guidance on the best way to work around this? Thanks in advance!