topic Re: Remove duplicates from string in QlikView

Remove duplicates from string

Anonymous — Tue, 04 Nov 2014 16:24:29 GMT

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.

Re: Remove duplicates from string

its_anandrjs — Tue, 04 Nov 2014 16:39:08 GMT

Hi,

Load your field with DIstinct key word

Source:

LOAD distinct * Inline

[

Number

];

Regards

Anand

Re: Re: Remove duplicates from string

Anonymous — Tue, 04 Nov 2014 16:57:13 GMT

Would something like this help ?

load

subfield ( String , ',' ) as Sub ,

String

;

LOAD * INLINE [

String

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

];

Re: Remove duplicates from string

Tue, 04 Nov 2014 17:52:51 GMT

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;

Re: Remove duplicates from string

Anonymous — Tue, 04 Nov 2014 18:28:34 GMT

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

Re: Remove duplicates from string

maxgro — Tue, 04 Nov 2014 18:46:51 GMT

RESULT

SCRIPT

load * inline [

string

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

];

load newstring, rowno() as id;

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

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

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

drop table b;