21 December 2022 Sorting data in Qlik Sense 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 sorting data in Qlik Sense. Many responses again and as expected nobody was fooled by the answers. You all got it right! The correct answer is A Sorting data When receiving data it might happen that data is unsorted. Mostly when receiving custom/hand-made files. While sorting is possible in the front end it is good practice to have the data sorted in the stored data as well. Furthermore it might happen that certain aggregations or calculations are made in the script based on a certain sorting. Here you can think of accumulating sales over time. In order to properly sort this data the order by clause can be added to the load script. Order by The order by clause is quite simple: Order by fieldname [ sortorder ] { , fieldname [ sortorder ] } You will tell Qlik to order the table based on the field given. It is possible to sort on more fields, these are added after a comma. It is also possible to influence the sort order, by specifying whether it is ascending (asc) or descending (desc). A order by on more fields based on this question could look like the following: Order by Date, Product, Price Desc This means we order the table by Date, then Product and finally Price Descending. So if we have products on the same date these are sorted from highest to lowest selling price. Things to keep in mind Order by will only work on resident tables. So first do a load from the data source and use this as a temporary table to do the sorting on. It will also not work on loads from files or as a preceding load. (for more info on preceding loads check out this blog post!) The default sort order is ascending, so numerical low to high and from alphabet a to z. It is not necessary to specify this in the order by clause, however for clarity sake it might be wise. Then use asc to specify ascending order or desc for the other way around. When making changes in the load of the resident table be aware that if you use an alias (as) you need to specify the original name of the field. The order in which you specify the order by clause determines the order in which the ordering is done. So in the example above the table will first be sorted on Date then Product and then Price descending. Keep this in mind if you get unexpected results. That’s it for this week! 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 Qlik 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 25 April 2024 Over 3000 Qlik Sense servers still vulnerable to Cactus ransomware In November of last year we reported on how two critical vulnerabilities in Qlik Sense Enterprise for Windows were exploited in the Cactus ransomware campaign. As of today, over 3000 vulnerable Qlik Sense servers remain connected to the Internet. New Release Qlik Vulnerability 29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution 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
25 April 2024 Over 3000 Qlik Sense servers still vulnerable to Cactus ransomware In November of last year we reported on how two critical vulnerabilities in Qlik Sense Enterprise for Windows were exploited in the Cactus ransomware campaign. As of today, over 3000 vulnerable Qlik Sense servers remain connected to the Internet. New Release Qlik Vulnerability
29 March 2024 Dealing with missing and late arriving values with OtherSymbol and other solutions Learn effective strategies for handling missing or late-arriving dimension values in Qlik with our expert guide. Discover how to use OtherSymbol, WHERE NOT EXISTS, and other solutions to deal with early-arriving facts Data Model Friday Qlik Test Prep Qlik Solution
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