Sorting data 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 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

Qlik Cloud Backup

Protect your investment in Qlik with daily incremental backups stored in an encrypted environment with redundant storage. Available for as little as 2 Euro per day. Learn more.

Join the team!

Do you want to work within a highly-skilled, informal team where craftsmanship, ingenuity, knowledge sharing and personal development are valued and encouraged? Check out our job openings.