topic Distinct load data based on one column in QlikView

Distinct load data based on one column

Fri, 06 Dec 2013 05:58:06 GMT

Hello,

I want to load data from excel which should be distinct based on one column. When i am trying load distinct (all column names) it is loading all data as it is. When i am using load distinct (one column) than it is loading distinct values of that column. But i want to remove all other column values based on one column as distinct. Could any one help me??

Re: Distinct load data based on one column

Fri, 06 Dec 2013 06:02:56 GMT

load:

a as b

from xml

where a <> ;

load:

a as c

from xml

where a = ;

no?

Re: Distinct load data based on one column

jonathandienst — Fri, 06 Dec 2013 06:06:19 GMT

That depends on what you want to do with the multiple values in the other columns.

You could concatenate them:

LOAD Distinct myColumn,

Concat(Column2, ',') As Column2,

Concat(Column3, ',') As Column3,

FROM ....

Group By myColumn;

You could use Min/Max etc or MinString/MaxString:

LOAD Distinct myColumn,

Min(Column2) As Column2,

MaxString(Column3) As Column3,

FROM ....

Group By myColumn;

You could create a table myColumn and a child table for the other fields:

myColumn:

LOAD Distinct myColumn From ....;

ChildColumns:

LOAD * From ....;

You could load the first instance of each value and ignore the others:

LOAD myColumn,

Column2,

...

FROM ...

Where Not(Exists(myColumn));

Regards

Jonathan