Getting the first value in Qlik with FirstSortedValue()

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:

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:

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?

More from the Bitmetric team

Bitmetric Qlik Support

Keep your Qlik environment running smoothly with proactive support that prevents issues before they appear. Available on flexible monthly plans. Learn more.

Qlik vs Power BI Series

See how Qlik and Power BI perform when theory meets reality. Three apps rebuilt, every step examined. Learn more.