Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Re: Choose top N values by groups

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;

14 Replies
MVP
MVP

Re: Choose top N values by groups

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

Re: Choose top N values by groups

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.

MVP
MVP

Re: Choose top N values by groups

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

Re: Choose top N values by groups

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


MVP
MVP

Re: Choose top N values by groups

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

Re: Choose top N values by groups

I want to get 3 last purchases for each ID

MVP
MVP

Re: Choose top N values by groups

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

Re: Choose top N values by groups

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

MVP
MVP

Re: Choose top N values by groups

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