topic Find distinct Values in a cell in QlikView

Find distinct Values in a cell

Sun, 12 Jun 2016 10:31:36 GMT

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?

Re: Find distinct Values in a cell

swuehl — Sun, 12 Jun 2016 12:08:14 GMT

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

tamilarasu — Sun, 12 Jun 2016 12:18:04 GMT

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;