topic Re: Choose top N values by groups in QlikView

Choose top N values by groups

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Choose top N values by groups

swuehl — Tue, 07 Feb 2017 19:57:09 GMT

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;

Re: Choose top N values by groups

Wed, 08 Feb 2017 10:37:34 GMT

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.

Re: Choose top N values by groups

swuehl — Wed, 08 Feb 2017 12:06:42 GMT

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?

Re: Choose top N values by groups

Wed, 08 Feb 2017 12:37:08 GMT

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

Re: Choose top N values by groups

swuehl — Wed, 08 Feb 2017 12:57:40 GMT

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?

Re: Choose top N values by groups

Wed, 08 Feb 2017 13:08:13 GMT

I want to get 3 last purchases for each ID

Re: Choose top N values by groups

swuehl — Wed, 08 Feb 2017 16:15:59 GMT

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.

Re: Choose top N values by groups

Wed, 08 Feb 2017 16:44:12 GMT

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

Re: Choose top N values by groups

swuehl — Wed, 08 Feb 2017 16:50:00 GMT

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

Re: Choose top N values by groups

Wed, 08 Feb 2017 17:02:56 GMT

return this

https://drive.google.com/file/d/0B1D9cwJJCyZ2QVk0Q3JFbV9qRk0/view?usp=sharing

Re: Choose top N values by groups

swuehl — Wed, 08 Feb 2017 17:23:23 GMT

Are you using a table box to view your data?

The table box will only show distinct combinations of field values, so it will not show any duplicates.

Since the rank will create a new field, this might add the "distinctness" to your records.

Please check your source data, I assume that your source data already shows the duplicates (e.g. add a Rowno() in your script or a Count(ID) as expression in a straight table)

Other than that, your records don't seem to be sorted correctly when ranked, right?

Again, if possible, post some sample data that can be imported into QV.

Re: Choose top N values by groups

Wed, 08 Feb 2017 17:36:49 GMT

I used count. If I load data without autonumber - all is ok, but if I use it, some fields dulicates.

The sorting is correct.

Unfortunately, I have no way to send real data.

Re: Choose top N values by groups

swuehl — Wed, 08 Feb 2017 20:23:43 GMT

Not sure what you mean with that.

Uploading a Sample

I asked for some real data to look at, for example the data you are already showing in your linked images, but we need to look at the source data that and your script.

Re: Choose top N values by groups

Thu, 09 Feb 2017 18:35:56 GMT

You are rigth. The reason was in the data.