Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Show all rows with highest value of FieldB w.r.t each FieldA

Hi,

The data table is as shown below and I am trying to create a table with rows which has only top value of "altB" for each category  -

I could achieve till this, i.e., find the top values of "altB" with respect to each category-

I am stuck here, how do I show all rows where category & altB are as shown in the above table? The table I am looking for should look like this-

PFA a sample app. I am trying to solve this without any changes to be made on the datamodel/script.

Tags (3)
1 Solution

Accepted Solutions
Highlighted

Re: Show all rows with highest value of FieldB w.r.t each FieldA

Another option is to create a hidden expression like this

If(altB = Max(TOTAL <category> altB), 1)

With all three dimensions

Capture.PNG

View solution in original post

6 Replies
Highlighted
Champion II
Champion II

Re: Show all rows with highest value of FieldB w.r.t each FieldA

Try below

=if(altB=AGGR(NODISTINCT Max(altB),category),altB)

Highlighted
Champion II
Champion II

Re: Show all rows with highest value of FieldB w.r.t each FieldA

Alternatively you can create a ROWKEY in script that identifies each row uniquely

and use the expression below

Data:

LOAD *,Rowno() as ROWKEY INLINE [

    category, altB, Field

    A, 1, aa

    A, 2, bb

    A, 3, cc

    B, 1, dd

    B, 1, ee

    C, 2, ff

    C, 3, gg

    C, 1, hh

    C, 4, ii

    D, 1, jj

    E, 2, kk

    E, 3, ll

    F, 2, mm

    F, 4, nn

    F, 1, oo

];

Expression:

=Max({<ROWKEY={"=altB=AGGR(NODISTINCT Max(altB),category)"} >} altB)

Highlighted

Re: Show all rows with highest value of FieldB w.r.t each FieldA

Another option is to create a hidden expression like this

If(altB = Max(TOTAL <category> altB), 1)

With all three dimensions

Capture.PNG

View solution in original post

Highlighted
Creator II
Creator II

Re: Show all rows with highest value of FieldB w.r.t each FieldA

HI Sunny,

This works. Thank you.

Highlighted
Creator II
Creator II

Re: Show all rows with highest value of FieldB w.r.t each FieldA

Hi Vineeth,

Thank you for the response.

For some reason I couldn't get this working. I added this as an expression, but not working.

Highlighted
Creator II
Creator II

Re: Show all rows with highest value of FieldB w.r.t each FieldA

Vineeth,

I prefer to do this without implementing any changes on the script side, and that's what I have also mentioned in my question.

Thank you.