GROUP BY ignoring a dimension

[micheledenardi]

for example i have this table:

itemgroup | description | price
A, a, 10
A, b, 12
A, c, 14
B, g, 11
B, h, 16

now i want to select the rows with the highest price in one group like this:

A, c, 14
B, h, 16

The qlik load (is fully functional) wich gets me near this is:

FinalTable:

Load 
    itemgroup, 
    MAX( price ) as price
FROM source.qvd 
    GROUP BY itemgroup;

A, 14
B, 16

By trying this I get an "Invalid expression"-error (rightly, because i'm grouping by description):

FinalTable:
Load 
    itemgroup, 
    description, 
    MAX( price ) as price
FROM source.qvd 
    GROUP BY itemgroup;

I need something like this pseudo query:

FinalTable:
Load
    itemgroup, 
    IGNORE( description) as description, 
    MAX( price ) as price
FROM source.qvd 
    GROUP BY itemgroup;

So, i found this solution. By using itemgroup and price as keys and performing a left join to add description field. This solution works only beacuse itemgroup and price are key fields, but i would like to know if exists a better solution.

FinalTable:
Load
    itemgroup, 
    MAX( price ) as price 
FROM source.qvd
     GROUP BY itemgroup;

left join(FinalTable)
Load 
    itemgroup,
    price,
    description

FROM source.qvd;

Does exists a better solution to achieve this ?

[petter · Accepted solution]

Yes you can do this:

LOAD

  itemgroup,

  FirstSortedValue(description,-price) AS description,

  Max(price) AS MaxPrice

FROM

  .......

GROUP BY

  itemgroup;

[sunny_talwar]

Another option

Table:

LOAD itemgroup,

    description,

    price

FROM ....;

Right Join (Table)

LOAD itemgroup,

    Max(price) as price

Resident Table

Group By itemgroup;