How to get real distinct value.

[NZFei]

Hello

Here are my scripts:

TestTemp:

LOAD * INLINE [
F1, F2, F3
1, a, 4234
2, aaaa, 2342
2, b, 24
3, c, 34
4, c, 64
5, deee, 5345
5, e, 6754
5, e, 545
];

Test:
load Distinct
F1,
F2
Resident TestTemp;

Drop Table TestTemp;

The result is:

F1	F2
1	a
2	aaaa
2	b
3	c
4	c
5	deee
5	e

What I want is distinct F1. So F1 value should be 1 2 3 4 5. Because F2 has two values for 2 and 5, 2 and 5 in F1 appeared twice.

I want to only take the first value  in F2 as F2, so that the final result is

F1	F2
1	a
2	aaaa
3	c
4	c
5	deee

How can I achieve that? Can anyone help please? Thanks!

[adhudson · Accepted solution]

Hi,

     If you are using the DISTINCT keyword in a load statement, It means that to load unique rows in the table.

     i.e., the DISTINCT keyword works with respect to the whole row, but not the single column.

     Your requirement seems that you only want the unique values from F1 column.

     So you can do the filteration manually as given in the attachment.

Regards

Andrew Hudson

[NZFei]

Thank you very very much indeed, Andrew!

[#4]

Dear Andrew,

your file is really useful to me too.

It can be 100 times more useful if it is possible to set a criteria by which QV picks the 1st or the 2nd (or the 3rd) value of F2, when F1 is the same.

In the example above, for example, what if I would like QV to pick the F2 value if it is more than 1 character long?

Is it easy to integrate such a request to your above script?

I have the feeling that we are entering a realm of a script filled with too many IFs...

thanks!