Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

1 Solution

Accepted Solutions
Not applicable
Author

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;

View solution in original post

5 Replies
its_anandrjs

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

Anonymous
Not applicable
Author

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
Author

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;

Anonymous
Not applicable
Author

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

maxgro
MVP
MVP

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;