Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Choose top N values by groups

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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;

View solution in original post

14 Replies
swuehl
MVP
MVP

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;

Not applicable
Author

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
MVP
MVP

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?

Not applicable
Author

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
MVP
MVP

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?

Not applicable
Author

I want to get 3 last purchases for each ID

swuehl
MVP
MVP

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.

Not applicable
Author

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
MVP
MVP

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