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 below will help.  this will create a row for each value in your comma separated list and you can include other fields as needed to keep your relationship keys/additional attributes.

source_data:
load
key ,
subfield(field, ',') as field
;
load * inline [
key | field
1 | a
2 | a,b
]
(delimiter is '|')
;

[zjdoonj]

Hi Steve,

Thanks for responding.  If you see in your example value 'a' is coming twice. My requirement is to show Unique values and value should appear only once. Please let me know if that is possible?

Thanks,

[stevejoyce]

It's showing 2x in the data set which is what i wanted.  if you had a list box it would only show once.

i could always do a load distinct, but what i wanted was if i select 'a' in front-end it should relate to key 1 & 2.

if you want to juts get distinct list of unique values then this would do that:

source_data:
load distinct
subfield(field, ',') as field
;
load * inline [
key | field
1 | a
2 | a,b
]
(delimiter is '|')
;

[zjdoonj]

Once again thanks Steve. I am having some issues when putting up subfield function and not able to run the code. Can you please help fix the below code.  [ServiceGroup]  is the field we are talking about. Appreciate the help.

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

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

OrganizationUnit

From "XYZ"

[stevejoyce]

can you post sample input data and expected output.  i dont know how you are going to pick a single value in ServiceGroup when there is a list of values in 1 row.  

[zjdoonj]

As I mentioned in my first message. Right now there are multiple values coming in ServiceGroup separated by comma but the requirement is to show just a single unique value.  The screenshot above I posted in my first message shows you the current picture. The requirement is to not have multiple values and present single value on every row. The field being used is "Service Group". Can you just add subfield function with the script I posted above?

[zjdoonj]

Hi Steve,

After putting up "subfield" function I still see rows coming service group appearing with multiple service group separated by comma. Please see below:

In this scenario consulting contracts should go to "Consulting" service group and should be separate from Systems integration. Can you please help? below is how I am using your function in the load script.

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

Basically my requirement is to show contracts for respective service group separately not together.

Thanks,

[stevejoyce]

This is essentially what i originally said but i guess you can try this...

SamplTable:

load

[EntityInstanceId]

,[Market]

,legalkey

,OrganizationUnit

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

;

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]

Hi Steve,

I made the change as you described above but there is a problem now and numbers are wrong. Please see the screenshot below. As highlighted below "Consulting,System Integration" both had 9 contracts but after the change it added 9 contracts in "Consulting" and kept 9 for "System Integration" which means total 18.  The number of contracts should be same after the change but here it increased. Can you please advise if that can be fixed?

Before the change:

After the change: