topic Re: How to get distinct values in QlikView

How to get distinct values

Anonymous — Sat, 25 Feb 2017 16:54:10 GMT

Hi,

I have two columns with repeated values:

F1, F2

1, a

2, f

2, aaaa

3, b

4, bb

5, c

and how can i get the result shown below:

F1, F2

1, a

2, f

3, b

4, bb

5, c

Re: How to get distinct values

avinashelite — Sat, 25 Feb 2017 16:58:39 GMT

try the

FirstSortedValue()

Re: How to get distinct values

masismykola — Sat, 25 Feb 2017 17:00:34 GMT

Hi,¨

after Load statement write Distinct. This will get you only unique records.

Please mark as answered.

Thank you.

Re: How to get distinct values

Anonymous — Sat, 25 Feb 2017 17:51:26 GMT

Hi Avinash,

I tried your solution, but am missing something.

Load * inline [F1, F2

1, a

2, f

2, aaaa

3, b

4, bb

5, c

5, c];

load2:

NoConcatenate

load F1,

firstsortedvalue(F2,F1) as f2 Resident Load

group by F1;

Drop table Load;

It doesnt work. Can you please tell me where i am doing it wrong??

Re: How to get distinct values

aarkay29 — Sat, 25 Feb 2017 18:15:15 GMT

Try this:

Table:

LOAD * INLINE [

F1, F2

1, a

2, f

2, aaaa

3, b

4, bb

5, c

];

Load

F1,

FirstValue(F2)

Resident

Table

Group by

F1;

Drop Table

Table;

Re: How to get distinct values

maxgro — Sat, 25 Feb 2017 18:24:59 GMT

source:

LOAD *, rowno() as r INLINE [

F1, F2

1, a

2, f

2, aaaa

3, b

4, bb

5, c

];

final:

NOCONCATENATE LOAD

F1, F2

RESIDENT source

WHERE F1 <> Peek('F1')

ORDER BY r

;

DROP TABLE source;

Re: How to get distinct values

MarcoWedel — Sun, 26 Feb 2017 00:30:12 GMT

Hi,

another solution could be:

LOAD * INLINE [
    F1, F2
    1, a
    2, f
    2, aaaa
    3, b
    4, bb
    5, c
    5, c
    5, c
] Where not Exists (F1);

hope this helps

regards

Marco

Re: How to get distinct values

Anonymous — Sun, 26 Feb 2017 16:29:19 GMT

Your solution worked for me!!!!

Re: How to get distinct values

Anonymous — Sun, 26 Feb 2017 16:30:42 GMT

Thank you Marco.

worked!!