topic Re: Only Display the highest record in a text object in QlikView

Only Display the highest record in a text object

Wed, 02 Apr 2014 10:42:58 GMT

Hi Community,

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

Airport

Airport	No. of times flown to
BEG	131
BHX	83
OTP	66
SRZ	61
LOS	55

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

Re: Only Display the highest record in a text object

MK_QSL — Wed, 02 Apr 2014 10:48:40 GMT

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

Re: Only Display the highest record in a text object

arjunkrishnan — Wed, 02 Apr 2014 10:51:26 GMT

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

MayilVahanan — Wed, 02 Apr 2014 10:51:37 GMT

Try like this

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

Re: Only Display the highest record in a text object

sudeepkm — Wed, 02 Apr 2014 10:55:21 GMT

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

Re: Only Display the highest record in a text object

Wed, 02 Apr 2014 11:05:17 GMT

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

my data set is actually

airport

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

Re: Only Display the highest record in a text object

MK_QSL — Wed, 02 Apr 2014 11:10:23 GMT

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

Re: Only Display the highest record in a text object

jonathandienst — Wed, 02 Apr 2014 11:22:29 GMT

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

HTH

Jonathan

Re: Only Display the highest record in a text object

pgrenier — Wed, 02 Apr 2014 11:49:01 GMT

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

Re: Only Display the highest record in a text object

Wed, 02 Apr 2014 12:17:02 GMT

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