Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
kavyadwh91
Contributor III
Contributor III

Remove repetitive values in string

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.

 

Labels (2)
3 Replies
martinpohl
Partner - Master
Partner - Master

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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