Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

thakrarm
New Contributor II

Remove duplicates from string

I have the following string contained in a field:

[1,3,4,8,10,27,27,28,29,12]

I'd like to be able to remove all the duplicate values within this string to end up with:

[1,3,4,8,10,27,28,29,12]

The string itself will always be in this format, with numbers ascending but can be of any length and have any number of duplicate values.  Is there a way to achieve this in the script?

Thanks.

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Re: Remove duplicates from string

You can do this in script something like below:

tmpRecords:

Load SourceStr,

    SubField(SourceStr, ',') as OneValue

Inline [

  SourceStr

  "1,3,4,8,10,27,27,28,29,12"

  "1,1,4,4,10,28,28,28,29,12"

  "1,3,4,5,5,7"

];

NewTable:

LOAD SourceStr,

    Concat(distinct OneValue, ',') as CleanStr

Resident tmpRecords

Group by SourceStr;

DROP Table tmpRecords;

5 Replies

Re: Remove duplicates from string

Hi,

Load your field with DIstinct key word

Source:

LOAD distinct  * Inline

[

Number

1

3

4

8

10

27

27

28

29

12

];

Regards

Anand

Re: Re: Remove duplicates from string

Would something like this help ?

load

  subfield ( String , ',' ) as Sub ,

  String

;

LOAD * INLINE [

  String

'1,3,4,8,10,27,27,28,29,12'

];

Not applicable

Re: Remove duplicates from string

You can do this in script something like below:

tmpRecords:

Load SourceStr,

    SubField(SourceStr, ',') as OneValue

Inline [

  SourceStr

  "1,3,4,8,10,27,27,28,29,12"

  "1,1,4,4,10,28,28,28,29,12"

  "1,3,4,5,5,7"

];

NewTable:

LOAD SourceStr,

    Concat(distinct OneValue, ',') as CleanStr

Resident tmpRecords

Group by SourceStr;

DROP Table tmpRecords;

thakrarm
New Contributor II

Re: Remove duplicates from string

Thanks Ersen, that's exactly what I was looking for.

MVP
MVP

Re: Remove duplicates from string

RESULT

1.png


SCRIPT

a:

load * inline [

string

'1,3,4,8,10,27,27,28,29,12'

];


b:

load newstring, rowno() as id;

load subfield(string, ',') as newstring resident a;


c:

load concat(newstring, ',', id) as newstring;

NoConcatenate load newstring, min(id) as id resident b group by newstring;

drop table b;