Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

Only Display the highest record in a text object

Hi Community,

I have a question regarding text objects. I have a list of airports flown to, please see below

Airport

AirportNo. of times flown to
BEG131
BHX83
OTP66
SRZ61
LOS55

I created this as a straight table so i'll need some sort of count in the text object. =Count({$<[Product] = {'Freight'} >}[To Airport]) so the raw data is BEG 131 rows BHX 83 rows and so on

As you know if I put this in a text object i would only get the total amount as there are no Dimensions 396 in this case but what I want is the Name of the airport so something like

='Most flown to Airport ' & 'BEG'

I hope this makes sense!

Thank you in advance

Tags (2)
9 Replies
MVP
MVP

Re: Only Display the highest record in a text object

='Most flown to Airport '  &FirstSortedValue(Airport,-[No. of times flown to])&CHR(10)&SUM([No. of times flown to])

arjunkrishnan
Contributor II

Re: Only Display the highest record in a text object

Use This Expression It Will Useful For U

='Most flown to Airport '  &FirstSortedValue(Airport,-aggr(sum(No. of times flown to),Airport))

Re: Only Display the highest record in a text object

Try like this

='Most flown to Airport ' &  FirstSortedValue(Airport, -[No. of times flown to])

sudeepkm
Valued Contributor III

Re: Only Display the highest record in a text object

you can also keep it in a script level. please take a look at the attachment.

Not applicable

Re: Only Display the highest record in a text object

Sorry I think the data I've given you is miss-leading.

my data set is actually

airport

BEG

BEG

BEG

BEG

BEG...

BHX

BHX
BHX

BHX....

and so on.

so i have to Count the number of BEG's and show BEG in the text object instead of the number of BEG's

MVP
MVP

Re: Only Display the highest record in a text object

='Most flown to Airport '  &FirstSortedValue(Airport,-Aggr(SUM([No. of times flown to]),Airport))&CHR(10)&SUM([No. of times flown to])

MVP
MVP

Re: Only Display the highest record in a text object

Hi

=FirstSortedValue(airport, -Aggr(Count(airport), airport))

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Partner
Partner

Re: Only Display the highest record in a text object

Hello Mark,

Presuming you might have an index column in your flight table (Flight_ID), I believe you could achieve your result using the following expression:

=FirstSortedValue({$<[Product] = {'Freight'} >}[To Airport], -Aggr({$<[Product] = {'Freight'} >} Count(DISTINCT Flight_ID), [To Airport]))

Regards,

Philippe

Not applicable

Re: Only Display the highest record in a text object

Hi,

Use this script

 

Tab:

Load * Inline [
Airport, No. of times flown to
BEG, 131
BEG, 100
BHX, 83
BHX, 88
OTP, 66
OTP, 60
SRZ, 61
LOS, 55
BEG, 80
]
;

Tab2:
Load
Max([No. of times flown to]) as MaxVal
Resident Tab;

Let vMax
= Peek('MaxVal',-1,'Tab2');

Drop Table Tab2;

Tab2:

Load

Airport as Airport_Max,

[No. of times flown to] as MaxTimesFlown

Resident Tab

Where [No. of times flown to]='$(vMax)';

In Text Object ---> =Airport_Max&' : ' &MaxTimesFlown