12 Replies Latest reply: Nov 11, 2017 5:23 AM by ramesh g

# FirstSortedValue()

Hi all,

i am new to qlikview just want to know that

how  FirstSortedValue() works in Qlikview... Can any one ive me a example ?

firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought from abc.csv

group by Customer;

???? i am unable to understand this through help also

Message was edited by: rohitkoul

• ###### FirstSortedValue()

Imagine you have the following table:

FieldA, FieldB

A, 3

B, 4

C, 1

D, 2

If you run a FirstSortedValue(FieldA, FieldB) you'll get the first sorted value from FieldA based on the sorting of FieldB. Value 1 is the first value for FieldB so the function will in this example return C.

• ###### FirstSortedValue()

In that example it loads the instance of the PurchasedArticle based on the earliest OrderDate, i.e. the first product bought.

• ###### Re: FirstSortedValue()

Hi Johannes

I woud like to get the last value for each customer.

If i use the firstSortedValue function, it return the first value but i need to get the last purchase for each customer.

For example

Customer      orderDate

Miguel           21/01/2014

Jose             20/06/2013

Carlos          20/06/2013

Miguel          20/11/2014

I want the output table look like this

Customer      orderDate

Miguel          20/11/2014

Jose             20/06/2013

Carlos          20/06/2013

• ###### Re: FirstSortedValue()

You can solve by

firstsortedvalue(Customer,-orderDate)

if we write - sign before the sort-weight i.e. orderDate here, it will return the last value.

• ###### Re: FirstSortedValue()

hi...Julisy...

I guess in your case only group by function will solve your purpose...to get desired out put...

try this

old:

orderdate

FROM

[C:\xx\Bi testing function.xlsx]

(ooxml, embedded labels, table is [group by]);

new:

Distinct customer,

max (date (orderdate))

resident old

Group by customer;

DROP Table old;

• ###### FirstSortedValue()

Thanks Johannes

if if(FirstSortedValue( likes ,-record_date,8)<>'',FirstSortedValue( likes ,-record_date)-FirstSortedValue( likes ,-record_date,2))

• ###### Re: FirstSortedValue()

Nice!

• ###### Re: FirstSortedValue()

What happens if there are null values in the data. are those going to come first or those go to the end.

E.g.

FieldA, FieldB

A, 3

B, 4

C,               //note FieldB has null for C

D, 2

• ###### Re: FirstSortedValue()

We can able to suppress the nulls as per the  measure filed