Getting the first value in Qlik with FirstSortedValue()

The Qlik function FirstSortedValue() gets the first value for a field, sorted by another field.

Every Friday at Bitmetric we’re posting a new Qlik certification practice question to our LinkedIn company page. Last Friday we asked the following Qlik Data Architect certification practice question about the FirstSortedValue() function in Qlik:

Based on the table below, a Data Architect needs to extract only the EmployeeID for the first contact the Customer has had. Which function should the Data Architect use top get the corresponding row?

The correct answer is D: FirstSortedValue()

Understanding the question and understanding what answer the examiner wants to hear on a certification exam are sometimes two different things. Qlik certication exam questions often contain some ambiguity, and as we’re striving for realism here, we include this in our questions as well. In the case of this question the correct answer is FirstSortedValue(). To understand why, let’s examine the question a bit closer and interpret what the examiner might want to hear.

The question is ‘What is the question?’

The request is to retrieve the first employee that had contact with the customer. As a general rule, anything that can be fixed in the script should be fixed in the script, so our first assumption will be that it needs to be fixed within the script.

Some other observations:

Looking at the table provided there are a few possibilities to find out who the first employee was, namely the TimeStamp or the Appointment Type.
Looking at the possible answers, there are no mentions of any temporary tables or joins, only the script functions are provided. Therefore, in this case, the simplest solution will be the best.

So we have the requirement of getting the first EmployeeID per CustomerID, without any further explicitly stated requirements. This leads us to pick the simplest solution, which is answer D, FirstSortedValue(). We could achieve the same result with Min(), but it would require a more complex script.

Having analyzed the question, let’s do some analyzing of the correct answer 😉

Using FirstSortedValue() we can retrieve the first employee in one quick load:

Qlik script demonstrating the use of the FirstSortedValue() aggregation function.

The nice thing of using FirstSortedValue() is that we can precisely determine the sort-weight, or sort order, we would like to use. In this case we use timestamp.

FirstSortedValue() used the following syntax:

FirstSortedValue ([ distinct ] value, sort-weight [, rank ])

Reviewing our script we see that EmployeeID is the value we would like to have returned. The TimeStamp is the sort-weight we would like to evaluate the EmployeeID by.

As FirstSortedValue() is an aggregation function, we need to specify which column(s) to group the result by. In this case we would like to see which Employee was the first one to contact a customer, so we need to do a GROUP BY [CustomerID] to aggregate the data at the right level.

Things to note about FirstSortedValue()

Now a few other things to keep in mind about FirstSortedValue. We can also rank the search. So by altering the expression to FirstSortedValue([EmployeeID], [TimeStamp], 2) we will get the second timestamp and the corresponding EmployeeID. If we change the two to a three, we will get the third timestamp and so on. Aha! So if we change it to -1, we would see the last Employee? Right? Unfortunately this doesn’t work like that in this case. To find the ‘LastSortedValue’ we have to put a ‘-‘ in front of the sort weight; FirstSortedValue([EmployeeID], -[TimeStamp]). In this way we find the last value.

Last thing to keep in mind is the DISTINCT in the syntax. It is possible in some cases that there is a possibility of more values corresponding to the same sort weight. This will result in a NULL value. To prevent this, you can use distinct in front; FirstSortedValue(DISTINCT [EmployeeID], [TimeStamp]).

That’s it for this week. See you next Friday?