topic Re: Eliminate multiple rows of data based on the highest value. in QlikView

Eliminate multiple rows of data based on the highest value.

Tue, 26 Jan 2016 12:54:15 GMT

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.

Re: Eliminate multiple rows of data based on the highest value.

swuehl — Tue, 26 Jan 2016 12:56:43 GMT

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

=Max(Amount)

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

Re: Eliminate multiple rows of data based on the highest value.

Peter_Cammaert — Tue, 26 Jan 2016 13:01:04 GMT

Or, depending on the value range of RSF:

=Max(Amount)

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

Re: Eliminate multiple rows of data based on the highest value.

Tue, 26 Jan 2016 13:20:22 GMT

here are my 2 expressions,but it is displaying blank

=max(tOTALaMOUNT)

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

Thank much..I highly appreciate.

Re: Eliminate multiple rows of data based on the highest value.

swuehl — Tue, 26 Jan 2016 13:22:20 GMT

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.

Re: Eliminate multiple rows of data based on the highest value.

swuehl — Tue, 26 Jan 2016 13:25:06 GMT

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?

Re: Eliminate multiple rows of data based on the highest value.

Tue, 26 Jan 2016 13:41:56 GMT

tOTALaMOUNT is nothing but Amount.

Re: Eliminate multiple rows of data based on the highest value.

swuehl — Tue, 26 Jan 2016 13:47:04 GMT

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

Re: Eliminate multiple rows of data based on the highest value.

Tue, 26 Jan 2016 13:51:32 GMT

PFA DATA FILE swuehl‌

Re: Eliminate multiple rows of data based on the highest value.

swuehl — Tue, 26 Jan 2016 14:03:25 GMT

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

Re: Eliminate multiple rows of data based on the highest value.

Tue, 26 Jan 2016 14:14:46 GMT

This is what I am getting

Re: Eliminate multiple rows of data based on the highest value.

swuehl — Tue, 26 Jan 2016 14:17:49 GMT

Since this data is not contained in your Excel sample data set, what's wrong with it?