Eliminate multiple rows of data based on the highest value.

[#1]

I have 5 columns

ID                  Name                Amount        RSF

1                    abc                     2345           543

1                    bcd                     5432           345

1                    cde                     3456           231

2                    efg                      7654           321

2                    EFT                    7654           567

3                    fgh                      3421           560

Here I want the o/p as below

ID               Name                  Amount          RSF

1                    bcd                     5432           345

2                    efg                      7654           567

3                    fgh                      3421           560

For ID '1' I want single row of data based on highest Amount AND also If amount is same I want it based on the highest RSF(Ex: ID '2')

Thanks much.

Also,I have this in Straight table.

[swuehl]

Create a straight table chart with dimensions ID and Name and two expressions:

=Max(Amount)

=FirstSortedValue(RSF,  -( Amount + RSF/1E6) )

[Peter_Cammaert]

Or, depending on the value range of RSF:

=Max(Amount)

=FirstSortedValue(RSF,  -( Amount*1000 + RSF) )

[#4]

here are my 2 expressions,but it is displaying blank

=max(tOTALaMOUNT)

=FirstSortedValue(BuildingRSF,-(tOTALaMOUNT *1000 + BuildingRSF) )

Thank much..I highly appreciate.

[swuehl]

What is tOTALaMOUNT ?

In your sample data, the field is called Amount. Please also note that QV is case sensitive with regard to field names.

[swuehl]

I also just noticed that Name shouldn't be a dimension, it needs to be a third expression

=FirstSortedValue(Name,  -( Amount + RSF/1E6) )

Talking about Amount, is Amount a field value as shown above, or is it already an aggregated value?

Could you post a sample of your raw data table?

[#7]

tOTALaMOUNT is nothing but Amount.

[swuehl]

If your field is called tOTALaMOUNT,

=max(tOTALaMOUNT)

should return a value, as long as the field shows numeric values, not only text values.

FirstSortedValue() need a numeric values as sort weight, thus the combination  -(tOTALaMOUNT *1000 + BuildingRSF) should return numbers, too.

FirstSortedValue() also might return NULL if there are more than one tOTALaMOUNT value per min. (tOTALaMOUNT *1000 + BuildingRSF), but I don't see this in your sample data set. You can add a DISTINCT qualifier to get a value returned in these cases:

=FirstSortedValue(DISTINCT BuildingRSF,-(tOTALaMOUNT *1000 + BuildingRSF) )

[#9]

PFA DATA FILE swuehl‌

[swuehl]

Just worked fine for me:

ID	FirstSortedValue(NAME, -(AMOUNT +RSF/1E6) )	max(AMOUNT)	FirstSortedValue(RSF, -(AMOUNT +RSF/1E6) )
1	def	456	50
2	jkl	178	567
3	pqr	345	234