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

# Help with using SubField to split names and \$

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!

• ###### Re: Help with using SubField to split names and \$

The subfield function as used by you simply creates a new record for each value in the list. All the other fields of the record are duplicated. In this case you can decide to divide the Revenue amount by the number of names in the list: Revenue/(substringcount(Name,',')+1) as Revenue. That way you can sum it up for the Engagement dimension to get the original amount. If that's not good enough you'll have to get create with expressions like sum(aggr(avg(Revenue),Name,Engagement))

• ###### Re: Help with using SubField to split names and \$

Hi Gysbert,

I couldn't seem to get the  sum(aggr(avg(Revenue),Name,Engagement)) to give me the right number when I used it with my full data set (but I didn't spend too much time troubleshooting), but your other suggestion worked perfectly:

Revenue/(substringcount(Name,',')+1) as Revenue

Thanks again!

R.

• ###### Re: Help with using SubField to split names and \$

Could you create two tables instead, joined on Engagement?

e.g. Table one contains Engagement, Revenue

Table two contains Engagement, Name

• ###### Re: Help with using SubField to split names and \$

Hi Andrew,

Thanks for this suggestion. I had thought of joining two tables, but wanted to investigate if I could do this any other way. I ended up getting this to work using Gysbert's first suggestion. Seems I have a lot to learn about some of these functions!

Thanks,

R.

• ###### Re: Help with using SubField to split names and \$

The code : Trim( SubField([Name], ',')) as [Name]

really helped me a lot.  Thanks!