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: 
micheledenardi
Specialist II
Specialist II

GROUP BY ignoring a dimension

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 ?

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

Yes you can do this:

LOAD

  itemgroup,

  FirstSortedValue(description,-price) AS description,

  Max(price) AS MaxPrice

FROM

  .......

GROUP BY

  itemgroup;

View solution in original post

2 Replies
petter
Partner - Champion III
Partner - Champion III

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;