filtering on highest x for each y

gandalfgray — Thu, 02 Aug 2012 11:46:28 GMT

Hi!

Example data is like this:

t1:

LOAD * INLINE [

person, rowid, phone_no

Joe, 1, 1234567

Joe, 2, 7880833

Jack, 3, 8797400

Jack, 4, 5866969

];

What's the smartest way to create a new table t2 from t1 with only person and one phone_no per person, where the phone_no used is the one with highest rowid for each person:

Resulting table:

t2:

person, phone_no

Joe, 7880833

Jack, 5866969

Re: filtering on highest x for each y

gandalfgray — Thu, 02 Aug 2012 11:59:03 GMT

This is my way, but I wonder if there are better ways:

t2:

LOAD person,

max(rowid) As max_rowid

Resident t1

Group By person;

Left Join (t2)

LOAD person,

rowid As max_rowid,

phone_no

Resident t1;

Drop Field max_rowid;

topic Re: filtering on highest x for each y in QlikView

filtering on highest x for each y

Re: filtering on highest x for each y