GROUP BY ignoring a dimension

micheledenardi — Tue, 21 Aug 2018 08:17:34 GMT

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 ?

Re: GROUP BY ignoring a dimension

petter — Tue, 21 Aug 2018 09:34:54 GMT

Yes you can do this:

LOAD

itemgroup,

FirstSortedValue(description,-price) AS description,

Max(price) AS MaxPrice

FROM

.......

GROUP BY

itemgroup;

Re: GROUP BY ignoring a dimension

sunny_talwar — Tue, 21 Aug 2018 11:15:19 GMT

Another option

Table:

LOAD itemgroup,

description,

price

FROM ....;

Right Join (Table)

LOAD itemgroup,

Max(price) as price

Resident Table

Group By itemgroup;

topic Re: GROUP BY ignoring a dimension in App Development

GROUP BY ignoring a dimension

Re: GROUP BY ignoring a dimension

Re: GROUP BY ignoring a dimension