Hi, everyone. May be anyone know how to get table with top N values by groups during load data?
Solved! Go to Solution.
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?
I havr columns with ID, date and purchase value.
Where not isNull(profit)
// Load *
// Where Rank<=3
Date as newdate,
Purchase value as newpurchase,
AutoNumber (RecNo(),ID) as Rank
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
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?
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.
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