Multiple Values in Filter separated by comma

[zjdoonj]

Hi Engineers,

I have a Filter which displays the below values for Service Group field. 

My requirement  is to show single values like the one on the first row "Application Outsourcing" . Now the second row shows three values separated by a comma and thats how it is coming from database. Can we display them as single values like on the second row it should be only "Business Process Outsourcing" and so on for the rest of the values as well. The purpose is to show one unique value on every row rather showing multiple values separated by comma.

Please let me know if this is possible.

Thanks

[stevejoyce]

I think we are going back and forth between the same 2 questions.

How can you split multiple values into 1 single value?  if you have some methodology around which one to choose, what is it?

or do you want to evenly split amongst multiple values?  like 4.5 for Consulting and 4.5 for System integration?

or do you pick the first one?  or the last one? or a random one?

You have contracts associated to many service groups.  I would expect the sum of contracts for each service group to be more then the actual total number of contracts because of this.

[zjdoonj]

Hi Steve,

I understood your points above and agreed. I made the  change as you described above.  Couple of questions.

1.  if you see according to your logic "Consulting" is correct and thats how I wanted to see it 30+21=51 after the change.  But the logic is not working for " System Integration" in similar way and 21 contracts are not getting added to 9 contracts. Basically as per your logic "System Integration" should show 21+9=30 contracts after the change.  Also after the change "System Integration" appearing twice. Please refer below screen shot before and after the change. Can you please  help how to fix this?

2. In after the change screenshot, if you see "System Integration" appearing twice in the filter. I added distinct but that does not work. Is there a way we can show it once instead twice.

Before the change:

After the Change:

Thanks for the help.

[zjdoonj]

Hi Steve,

Any help here?

Thanks,

[stevejoyce]

Maybe this is due to blank spaces.  What if you wrap your subfield in a trim(subfield([ServiceGroup], ','))

[zjdoonj]

Hi Steve,

Thank you so much with the help. Now the user want me to display data like below. Can we tweak it a bit and do this?

Filter should always display  single values for Service Group selection like below which currently it is showing with subfield function:

But when user select one of the value in the filter above e.g "Consulting", the table should show all the rows where there is "Consulting". Like from below data set it should show two rows "Consulting" and "Consulting,System Integration". Is that possible.

I really appreciate your help on this.

Thanks,

[stevejoyce · Accepted solution]

Add the original field to your load statement that has the subfield, give it an alias.  Your filter will be the distinct list, your table will be the original comma seperated list

See update below script.

Filter object uses [ServiceGroupValues]

Table uses [ServiceGroup]

SamplTable:

load

[EntityInstanceId]

,[Market]

,legalkey

,OrganizationUnit

,[ServiceGroup]

,subfield([ServiceGroup], ',') as [ServiceGroupValues]

;

LOAD
[EntityInstanceId],
IF(Isnull([Market]),'Incomplete',[Market]) as [Market],
Num(Num#([LegalTeam])) as legalkey,

IF(Isnull([ServiceGroup]),'Incomplete',[ServiceGroup]) as [ServiceGroup],

OrganizationUnit

From "XYZ"

[zjdoonj]

This worked. Thank you Steve.