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: 
divya_anand
Creator III
Creator III

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.

1 Solution

Accepted Solutions
sunny_talwar

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
vinieme12
Champion III
Champion III

Try below

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

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)

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
sunny_talwar

Another option is to create a hidden expression like this

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

With all three dimensions

Capture.PNG

divya_anand
Creator III
Creator III
Author

HI Sunny,

This works. Thank you.

divya_anand
Creator III
Creator III
Author

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.

divya_anand
Creator III
Creator III
Author

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.