I’ve used FirstSortedValue before in QlikView application although it’s been one of those functions I never really investigated.

You can use FirstSortedValue both in the script and user interface (UI) of QlikView, this post focuses on the script element. A very good post on using the function in the UI can be found on the Qlik community (Customisable Straight (and Pivot) Tables – more food for thought http://community.qlik.com/docs/DOC-6046 – Jason Michaelides)

FirstSortedValue works when loading aggregated (grouped) data and allows you to compare one field which you sort by to return the value of another.

Here we have a simple table showing when a customer bought a particular product:

QlikView FirstSortedValue

So the question FirstSortedValue can answer is, “what was the first product bought by each customer?”

Loading the data grouped by Customer we can use the DayOfSale as the sort-weight, position 1 and return the value of product.

Customer:
Load
             Customer
             ,FirstSortedValue(Product,DayOfSale,1) as FirstProductBought
Resident Customers_Temp
Group by Customer

QlikView FirstSortedValue

Instead of returning the product we could return the ID instead and create a link or join to the original table.

We can also increase the position number and return the second, third, forth, etc product bought for each customer. It’s at this point I think the function used in the way shown above breaks down.

For example, we do not know how many products each customer has bought and therefore we don’t know how many lines of FirstSortedValue to add. Do we add 100 just to be sure? Seems a waste and we would probably end up with many columns unused.

Remembering some work I did recently on adding flags to a calendar table I decided to use a similar approach here which results in a table as shown:

QlikView FirstSortedValue

The code (in tab Transform A) found in the QlikView Community  runs through three steps:

1. Get the max count (DayOfSales) for the data source
2. Loops through from 1 to the max creating a new flag field which links back to the original data
3. Ensures the null values created in step 2 are changed to zero values.

We can use the same method to create a single field with the buying order:

QlikView FirstSortedValue

The example has four steps and shown in the Transform B tab:

1. Get the max count (DayOfSales) for the data source (reused from Transform A Tab)
2. Add a default Buying_Order value (zero) to the original records
3. Loops through from 1 to the max concatenating the results creating adding the rank value to Buying_Order only where the Buying_Order hasn’t already been set.
4. Join the data back to the original table where Buying_Order>0, this removes anyline of data that shouldn’t be there as that customer had less purchased than the max possible.

I hope this is helpful, please let me know if you have used firstsortedvalue in the code in some other way

Richard