topic Re: Get record holds the highest number in QlikView

Get record holds the highest number

renjithpl — Sat, 10 Sep 2016 19:35:35 GMT

Hi All,

I have a table like this

ID	RandomNumber	Country
1	152	Australia
1	162	Canada

While loading i want

ID	RandomNumber	Country
1	162	Canada

to get only one record for an ID with highest RandomNumber. My output should look like

Any ideas.?

Re: Get record holds the highest number

swuehl — Sat, 10 Sep 2016 19:55:21 GMT

LOAD ID,

Max(RandomNumber) as RandomNumber,

FirstSortedValue(Country, -RandomNumber)

FROM ...

GROUP BY ID;

Table:

LOAD ID, RandomNumber, Country

FROM ....;

INNER JOIN

LOAD ID, Max(RandomNumber) as RandomNumber

RESIDENT Table

GROUP BY ID;

Re: Get record holds the highest number

renjithpl — Sat, 10 Sep 2016 20:09:08 GMT

I am getting both the records

Re: Get record holds the highest number

renjithpl — Sat, 10 Sep 2016 20:10:28 GMT

The second one is working, let me verify. THanks much.

Re: Get record holds the highest number

Sat, 10 Sep 2016 20:11:52 GMT

LOAD *

SQL select

ID ,RandomNumber, Country

from table where RandomNumber=(select max(RandomNumber) from table) ;

try the above

Re: Get record holds the highest number

swuehl — Sat, 10 Sep 2016 20:14:40 GMT

Both should work.

Working samples:

Table:
LOAD ID, Max(RandomNumber) as RandomNumber, FirstSortedValue(Country, -RandomNumber) as Country
 INLINE [
ID, RandomNumber, Country
1, 152, Australia
1, 162, Canada
]
GROUP BY ID;

Table:
LOAD * INLINE [
ID, RandomNumber, Country
1, 152, Australia
1, 162, Canada
];


INNER JOIN
LOAD ID, Max(RandomNumber) as RandomNumber
Resident Table
GROUP BY ID;