FirstSortedValue

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

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

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

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

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

3 responses to “FirstSortedValue

  1. Can we use First Sorted Value when we have only 1 Column in the table. I have a scenario where in the filter pane we display the Business Region Names. If the user has not selected any business region, i need to pick the first value in Filter Pane.

    Like

    • I tested once to see if the sort order would work on text values (a-z). It doesn’t so unless your values are numbers then you would need an additional column as the sort order. That should be an easy distinct load into another table using rowno() as the sort order field value.
      Rich

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s