Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gandalfgray
Specialist II
Specialist II

filtering on highest x for each y

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

1 Reply
gandalfgray
Specialist II
Specialist II
Author

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;