Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

Eliminate multiple rows of data based on the highest value.

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.

11 Replies
MVP
MVP

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

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.

Or, depending on the value range of RSF:

=Max(Amount)

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

Not applicable

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

here are my 2 expressions,but it is displaying blank

=max(tOTALaMOUNT)

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

Thank much..I highly appreciate.

MVP
MVP

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

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.

Highlighted
MVP
MVP

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

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?

Not applicable

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

tOTALaMOUNT is nothing but Amount.

MVP
MVP

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

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


Not applicable

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

PFA DATA FILE swuehl

MVP
MVP

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

Just worked fine for me:

ID FirstSortedValue(NAME, -(AMOUNT +RSF/1E6) ) max(AMOUNT) FirstSortedValue(RSF, -(AMOUNT +RSF/1E6) )
1def45650
2jkl178567
3pqr345234