topic Re: Firstsortedvalue question. in QlikView

Firstsortedvalue question.

Tue, 13 Oct 2015 12:19:52 GMT

I am trying to find out city for which I have maximum Applications. Each application has a unique id : AppId. My expression returns null.

Expression:

vFirstCity=FirstSortedValue (City,Aggr(Count(AppId),City ))

sunny_talwar — Tue, 13 Oct 2015 12:43:26 GMT

For max you will need a negative sign next to the Aggr function:

vFirstCity=FirstSortedValue(City, -Aggr(Count(AppId), City))

Tue, 13 Oct 2015 12:48:26 GMT

Thanks a lot for quick revert.Extremely silly mistake.Anyway to get any value if there are multiple cities with same count ?

Anonymous — Tue, 13 Oct 2015 12:52:33 GMT

Hi,

Yes for Maximum value,minus sign should be used.

vFirstCity=FirstSortedValue(City, -Aggr(Count(AppId), City))

if you get multiple values then FirstSortedValue() will return null.

sunny_talwar — Tue, 13 Oct 2015 13:22:20 GMT

You can use a Concat() function to see them all:

=Concat(Aggr(FirstSortedValue(City, -Aggr(Count(AppId), City)), City), ', ')

Anonymous — Tue, 13 Oct 2015 13:25:47 GMT

Hi,

Here is an extract from the Reference Manual. Hope this helps.

firstsortedvalue ( [ distinct ] expression [, sort-weight [, n ]] )

returns the first value of expression sorted by corresponding sortweight

when expression is iterated over a number of records as

defined by a group by clause. Sort-weight should return a numeric

value where the lowest value will render the corresponding value of

expression to be sorted first. By preceding the sort-value expression

with a minus sign, the function will return the last value instead. If

more than one value of expression share the same lowest sort-order,

the function will return null. By stating an n larger than 1, you will

get the nth value in order. If the word distinct occurs before the

expression, all duplicates will be disregarded.

Clever_Anjos — Tue, 13 Oct 2015 13:30:49 GMT

You can choose a second criteria, dividing by a large number

FirstSortedValue(City, -Aggr(Count(AppId), City) + count(otherfield)/10000 )

Tue, 13 Oct 2015 13:31:48 GMT

That makes sense. Thanks.