topic Re: Remove repetitive values in string in QlikView

Remove repetitive values in string

kavyadwh91 — Fri, 11 Jun 2021 14:15:07 GMT

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.

Re: Remove repetitive values in string

martinpohl — Fri, 11 Jun 2021 14:52:04 GMT

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;

Re: Remove repetitive values in string

rwunderlich — Fri, 11 Jun 2021 18:22:21 GMT

@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

Re: Remove repetitive values in string

kavyadwh91 — Tue, 15 Jun 2021 05:29:49 GMT

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