Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Find distinct Values in a cell

Hi all,

I have this table

Names
George, George, John, Chris
Helen, John
John, John, John,John

and i want to create a new column with this output

Output
George, John, Chris
Helen, John
John

any ideas?

1 Solution

Accepted Solutions
MVP
MVP

Re: Find distinct Values in a cell

You can do it like this (first splitting the field value into parts, then re-combining using a distinct concat):

Names:

LOAD Concat(DISTINCT Name,',') as Names

GROUP BY RecID;

LOAD recno() as RecID, Trim(SubField(Names,',')) as Name;

LOAD * INLINE [

Names

"George, George, John, Chris"

"Helen, John"

"John, John, John,John"

];

2 Replies
MVP
MVP

Re: Find distinct Values in a cell

You can do it like this (first splitting the field value into parts, then re-combining using a distinct concat):

Names:

LOAD Concat(DISTINCT Name,',') as Names

GROUP BY RecID;

LOAD recno() as RecID, Trim(SubField(Names,',')) as Name;

LOAD * INLINE [

Names

"George, George, John, Chris"

"Helen, John"

"John, John, John,John"

];

Re: Find distinct Values in a cell

Hi,

Try this,

Data:

Load Names, Trim(SubField(Names, ',')) as TempField;

LOAD * INLINE [

    Names

    "George, George, John, Chris"

    "Helen, John"

    "John, John, John,John"

];

Final:

LOAD Names,

    Concat(Distinct TempField, ',') as NamesNew

Resident Data

Group by Names;

Drop Table Data;

Untitled.png

Community Browser