Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

gandalfgray
Valued Contributor

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
Valued Contributor

Re: filtering on highest x for each y

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;

Community Browser