Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
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.

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