Remove repetitive values in string

[kavyadwh91]

Hi Everyone,

I need to remove the repetitive values in the string. Here is the example I am having data like this.

"Academic Medical Centers, Academic Medical Centers, Academic Medical Centers, Hospitals and Health Systems, Hospitals and Health Systems, Hospitals and Health Systems"

I need to display only Academic Medical Centers, Hospitals and Health Systems.

Please do the needful.

Thanks in advance.

[martinpohl]

the repetive value is in the string of one field?

Maybe this helps

Temp:

load

ID,

substring(Yourfield,',') as Content

resident your table;

left join (your table) load distinct

ID,

concat(ltrim(Content,', ') as NewContent

resident Temp

group by ID;

drop table Temp;

[rwunderlich]

@martinpohl Good solution. However, I would not do the distinct in

left join (your table) load distinct

as that will Distinct "your table" as well, which is not desirable. 

Instead I would do the distinct in the Concat function

concat(DISTINCT ltrim(Content,', ') as NewContent

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[kavyadwh91]

Thanks for your response. We have  repetitive values in many fields. I some fields it is repeating three times.

Industry:

1.Aerospace and Defense,Aerospace and Defense,Aerospace and Defense,Industrial Manufacturing,Industrial Manufacturing,Manufacturing / Engineering,Manufacturing / Engineering,Manufacturing / Engineering,Technology,Technology,Technology,Federal Contractors,Federal Contractors,Federal Contractors

2.Academic Medical Centers,Academic Medical Centers,Hospitals and Health Systems,Hospitals and Health Systems

Output expected:

1.Aerospace and Defense,Industrial Manufacturing,Manufacturing / Engineering,Technology,Federal Contractors

2.Academic Medical Centers,Hospitals and Health Systems