Taking the last n rows from a group by table

[shayd]

Hi all,

For the table above, created by a group by syntax, I would like to take the last n rows per name.
For example, For Smithy, I would like to create a code that will give me the last n rows, ordered by date of course.
Eventually, this is an example for a million-row table, ID grouped, from which I would like to take per each ID The latest K rows or so.
I need it to be done through the script.

Thanks! 

[Arthur_Fong]

You have a sample app?

[shayd]

Attached 🙂

[Arthur_Fong · Accepted solution]

Try this:

Script:

let n = 3;   //change this to your last n rows

Raw:
load * inline [
TimeStamp,Name,flag
11/09/2018 15:07:11,Emmy,First
21/09/2018 04:05:50,Emmy,
22/09/2018 17:08:12,Emmy,
07/01/2019 00:00:37,Emmy,
19/02/2019 00:07:58,Emmy,
12/12/2018 21:40:28,Liv,First
];

NoConcatenate
name:
LOAD
    Timestamp#("TimeStamp",'DD/MM/YYYY hh:mm:ss') as "TimeStamp",
    Name,
    flag
resident Raw;
drop table Raw;

tempnames:
LOAD Name,
	 TimeStamp,
     flag,
     Autonumber(TimeStamp, Name) as Rank
RESIDENT name
ORDER BY Name, TimeStamp desc;
DROP TABLE name;

NoConcatenate
names:
load * resident tempnames
where Rank <='$(n)';

drop table tempnames;
//Store names into [lib://names/names.qvd];

Change the inline table to your Excel connection path.

[shayd]

Worked exactly as I expected.
Thank you Arthur!