Help with using SubField to split names and $

[drminaker]

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.

Engagement	Revenue	Name
A	25,000	Ryan, Luka
B	10,000	Sarah

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]

Engagement	Revenue	Name
A	25,000	Ryan
A	25,000	Luka
B	10,000	Sarah

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!

[Gysbert_Wassenaar · Accepted solution]

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))

[#3]

Could you create two tables instead, joined on Engagement?

e.g. Table one contains Engagement, Revenue

Table two contains Engagement, Name

[drminaker]

Hi Gysbert,

Thanks for the reply!

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.

[drminaker]

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.

[gardan]

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

really helped me a lot.  Thanks!