21 April 2022 Getting the first value in Qlik with FirstSortedValue() Share this message 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? See you at QlikWorld 2023 in Vegas? Come see us at QlikWorld 2023 for a chance to win a free, lifetime SenseTheme subscription and get some cool swag. Want more Friday Qlik Test Prep? Check out the Friday Qlik Test Prep archive for more Qlik questions and answers. Friday Qlik Test Prep Functions Qlik Script How can we help? Feel free to contact us if you have any comments or questions. Call us Mail us 23 May 2023 What’s New in Qlik Sense May 2023 for Administrators This blog post provides Qlik Sense administrators a summary of the new administrative features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started on what’s new in Qlik Sense for May 2023. New Release Qlik 23 May 2023 What’s New in Qlik Sense May 2023 for Business Users, Analytic Creators and Data Integrators This blog post provides Qlik Sense business users, analytic creators, and data integrators a summary of the features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started with what’s new in Qlik Sense for May 2023. New Release Qlik 11 May 2023 How to make seasonal trendlines in Qlik Sense 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 how to make seasonal trendlines: The correct answer was B! Trend analysis Qlik has added Time series decomposition modifier functions to the line chart. This can […] Friday Qlik Test Prep Solution
23 May 2023 What’s New in Qlik Sense May 2023 for Administrators This blog post provides Qlik Sense administrators a summary of the new administrative features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started on what’s new in Qlik Sense for May 2023. New Release Qlik
23 May 2023 What’s New in Qlik Sense May 2023 for Business Users, Analytic Creators and Data Integrators This blog post provides Qlik Sense business users, analytic creators, and data integrators a summary of the features and improvements available in Qlik Sense Enterprise on Windows. Let’s get started with what’s new in Qlik Sense for May 2023. New Release Qlik
11 May 2023 How to make seasonal trendlines in Qlik Sense 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 how to make seasonal trendlines: The correct answer was B! Trend analysis Qlik has added Time series decomposition modifier functions to the line chart. This can […] Friday Qlik Test Prep Solution