topic Re: Help with using SubField to split names and $ in App Development

Help with using SubField to split names and $

drminaker — Wed, 18 May 2016 14:29:30 GMT

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!

Re: Help with using SubField to split names and $

Gysbert_Wassenaar — Wed, 18 May 2016 15:52:02 GMT

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 $

Wed, 18 May 2016 15:59:15 GMT

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 $

drminaker — Wed, 18 May 2016 17:51:04 GMT

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!

Re: Help with using SubField to split names and $

drminaker — Wed, 18 May 2016 17:53:06 GMT

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,

Re: Help with using SubField to split names and $

gardan — Wed, 03 Aug 2016 19:49:43 GMT

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

really helped me a lot. Thanks!