Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
drminaker
Contributor III
Contributor III

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!

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable

Could you create two tables instead, joined on Engagement?

e.g. Table one contains Engagement, Revenue

Table two contains Engagement, Name

drminaker
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III

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

really helped me a lot.  Thanks!