6 Replies Latest reply: Sep 14, 2017 4:36 AM by Divya anand

# 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.

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

Try below

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

• ###### 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.

• ###### 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)

• ###### 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.

• ###### 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

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

HI Sunny,

This works. Thank you.