topic How to get unique / distinct rows? in QlikView

How to get unique / distinct rows?

Mon, 11 May 2015 08:13:15 GMT

Hi,

Let's say I have the following 3 fields loaded from a file with many others:

ID	X	Y
1	10	a
2	10	b
3	10	c
4	5	d
5	5	e
6	5	f
7	5	g
8	5	h
9	15	i
10	15	j
11	20	k
12	20	l
13	20	m
14	20	n
15	25	o
16	25	p

For each distinct value of 'X' I would like to take only one of it's corresponding IDs (no matter which). Furthermore, I would like to keep the table above loaded as it is (with all of it's values).

Re: How to get unique / distinct rows?

robert_mika — Mon, 11 May 2015 08:53:36 GMT

t1:

LOAD ID,

FROM

[163430.xlsm]

(ooxml, embedded labels, table is Sheet1);

t2:

LOAD Min(ID),

X as x

from

[163430.xlsm]

(ooxml, embedded labels, table is Sheet1)

group by X

Re: How to get unique / distinct rows?

anbu1984 — Mon, 11 May 2015 08:56:41 GMT

T1:
Load * Inline [
ID,X,Y
1,10,a
2,10,b
3,10,c
4,5,d
5,5,e
6,5,f
7,5,g
8,5,h
9,15,i
10,15,j
11,20,k
12,20,l
13,20,m
14,20,n
15,25,o
16,25,p ];

T2:
Load X,Max(ID) as ID1 Resident T1 Group By X;

Re: How to get unique / distinct rows?

Mon, 11 May 2015 09:09:58 GMT

Thank you for your quick and appropriate answers. I already used what Robert Mika suggested at first and it was very helpful. Thank you anbu cheliyan for your cooperation as well

ID	X	Y
1	10	a
2	10	b
3	10	c
4	5	d
5	5	e
6	5	f
7	5	g
8	5	h
9	15	i
10	15	j
11	20	k
12	20	l
13	20	m
14	20	n
15	25	o
16	25	p

ID	X	Y
1	10	a
2	10	b
3	10	c
4	5	d
5	5	e
6	5	f
7	5	g
8	5	h
9	15	i
10	15	j
11	20	k
12	20	l
13	20	m
14	20	n
15	25	o
16	25	p

ID	X	Y
1	10	a
2	10	b
3	10	c
4	5	d
5	5	e
6	5	f
7	5	g
8	5	h
9	15	i
10	15	j
11	20	k
12	20	l
13	20	m
14	20	n
15	25	o
16	25	p