Choose top N values by groups

[#1]

Hi, everyone. May be anyone know how to get table with top N values by groups during load data?

[swuehl · Accepted solution]

Maybe something like this for N=10:

Top10:

LOAD *

WHERE Rank <= 10;

LOAD Group, Value, Autonumber(recno(), Group) as Rank

RESIDENT YourTable

ORDER BY Group, Value desc;

[#3]

Thanks. I don't know why, but I get some values twice, because Rank was calculated wrong. May be the reason is that I by another column.

[swuehl]

Vladimir Shatalov wrote:

Thanks. I don't know why, but I get some values twice, because Rank was calculated wrong. May be the reason is that I by another column.

You do what exactely by another column? I think there is a piece of information missing in your reply.

Could you post your script? And maybe some lines of sample data?

[#5]

I havr columns with ID, date and purchase value.

My script

Table1:

Load

Id,

Date,

Profit,

purchase value

From

(qvd)

Where not isNull(profit)

Top3:

// Load *

// Where Rank<=3

Load

ID,

Date as newdate,

Purchase value as newpurchase,

AutoNumber (RecNo(),ID) as Rank

Resident Table1

Order by ID, Date desc;

I comment the part of code to test.

When I looked at the table content, I noticed that all the records in which the rank is less than three and  except the first ID duplicated, but if I only upload table without autonumber, the records are not duplicated

[swuehl]

Not sure I understand.

I've noticed that you ordered your second table by ID, Date desc.

So you are essentially ranking the dates per ID, not the purchases. Is this what you want?

[#7]

I want to get 3 last purchases for each ID

[swuehl]

Do you have multiple purchases for any given ID on a same date?

This approach will just order the purchases by date descending and should rank them in order, but it should assign a unique rank per line (unique per ID group).

If you say that you are getting duplicate values, I can't see how this should duplicate anything. Maybe it's because your tables are still linked to other tables and the duplication occurs due to the QV logic?

If possible, post a small sample QVW or at least some input data.

[#9]

Do you have multiple purchases for any given ID on a same dat?

I hope, that no, because i have date accurate to seconds.

Maybe it's because your tables are still linked to other tables and the duplication occurs due to the QV logic?

Yes, I Load two tables. They linked by ID

My data looks like this https://drive.google.com/file/d/0B1D9cwJJCyZ2c3ZNdU82ek5Pdzg/view?usp=sharing

[swuehl]

Ok, and what is returned by your script using this data for rank?