topic Re: Firstsorted value in QlikView

Firstsorted value

Thu, 27 Dec 2012 16:42:42 GMT

Hi All,

What is the use of Firstsortedvalue()

please give me example?

Re: Firstsorted value

nagaiank — Thu, 27 Dec 2012 17:46:51 GMT

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.

Example:

Load Customer,

firstsortedvalue(PurchasedArticle, OrderDate)as

FirstProductBought

from abc.csv group by Customer;

Re: Firstsorted value

IAMDV — Thu, 27 Dec 2012 18:08:54 GMT

Hi Nagaraju,

I've made two videos on FirstSortedValue()

http://qlikshare.com/392

Thanks,

Re: Firstsorted value

jagan — Thu, 27 Dec 2012 18:12:28 GMT

Hi Nagaraju,

Suppose you have data like this

Name Sales

xxx 1000

yyy 2000

zzz 3000

Suppose if you want to get the name of salesperson who have highest then FirstSortedValue() will be useful

=FirstSortedValue(Name, Aggr(-Sum(Sales), Name)

Regards,

Jagan.

Re: Firstsorted value

Anonymous — Wed, 11 Dec 2013 16:06:05 GMT

Jagan,

Suppose the data is like this:

Name Sales

xxx 1000

yyy 2000

zzz 3000

mmm 3000

and you want to show at least one of the two highest i.e. either zzz or mmm, how can this be resolved ?

Re: Firstsorted value

Fri, 08 May 2015 06:39:07 GMT

Use this.....

Table1:

LOAD * Inline

[
Name, Sales
xxx, 1000
yyy, 2000
zzz, 3000
mmm, 3000
];
Final:
LOAD Sales, FirstSortedValue(Name, RecNo()) as Name1 Resident Table1 Group by Sales;