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? More from the Bitmetric team Take your Qlik skills to the next level! Since 2013, the Masters Summit for Qlik is the premier advanced training for Qlik. Join us in Vienna and take your Qlik skills to the next level. Join the team! Enjoying these challenges? We have many more, and we’ll even pay you to solve them 😉 Check out our job opening. Friday Qlik Test Prep Functions Qlik Script How can we help? Barry has over 20 years experience as a Data & Analytics architect, developer, trainer and author. He will gladly help you with any questions you may have. Call us Mail us 22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization 11 March 2024 5 ways to pass selections between Alternate States in Qlik Sense This guide delves into the intricacies of passing selections between Alternate States in Qlik Sense, offering a comprehensive look at techniques such as explicit and implicit assignments, merging selections, and utilizing granularity. Perfect for developers and data analysts, it equips you with the knowledge to leverage Alternate States effectively, enhancing dashboard functionality and enriching data analysis. Expressions Friday Qlik Test Prep Qlik 29 February 2024 What’s New in Qlik Sense February 2024 for Administrators This Qlik Sense February 2024 release provides Qlik Sense administrators a summary of the features and improvements available. New Release Qlik
22 March 2024 Picking the right color palette for your visualization Discover how to choose the perfect color palette for your data visualizations with our expert guide. Learn about single, categorical, sequential, and diverging color palettes to enhance your charts and graphs. Ideal for data analysts and visualizers. Friday Qlik Test Prep Qlik SenseTheme Visualization
11 March 2024 5 ways to pass selections between Alternate States in Qlik Sense This guide delves into the intricacies of passing selections between Alternate States in Qlik Sense, offering a comprehensive look at techniques such as explicit and implicit assignments, merging selections, and utilizing granularity. Perfect for developers and data analysts, it equips you with the knowledge to leverage Alternate States effectively, enhancing dashboard functionality and enriching data analysis. Expressions Friday Qlik Test Prep Qlik
29 February 2024 What’s New in Qlik Sense February 2024 for Administrators This Qlik Sense February 2024 release provides Qlik Sense administrators a summary of the features and improvements available. New Release Qlik