Sorting tables

We will now introduce the Order By statement, which is added to a Load statement and is used to sort an input table based on certain fields. There is one major condition for the Order By statement to work: it must be applied to a Load statement getting data from a Resident table, not from a table file or any other source.

Some databases can receive Order By instructions in the Select query, but in this section we will only deal with Order By statements on the QlikView side.

The Order By statement must receive at least one field name over which the ordering will be performed and, optionally, the sort order (either ascending or descending). If the sort order is not specified along with the field name, the default sort order will be applied, which is ascending.

An example script of an Order By statement at play is:

Load
 Region,
 Date,
 Amount
Resident SalesTable
Order By Date asc;

In this script, we are loading three fields (Region, Date, and Amount) from a previously loaded table, named SalesTable, and, as the table is being read, the data is being ordered by Date from older to newer records (ascending).

Ordering the Order-By fields

An important point to consider when using the Order By statement, is that not only can one field be specified as the sorting value, we can also, for instance, sort the table by Date from older to newer and by Amount from largest to smallest. The order in which we specify the sorting fields will determine the output of the operation. Take, for example the following two scripts:

A:
Load
 Region,
 Date,
 Amount
Resident SalesTable
Order By Date asc,
Amount desc;

B:
Load
 Region,
 Date,
 Amount
Resident SalesTable
Order By Amount desc,
Date asc;

The difference between both scripts is the Order by clause. Look closely and you will find that, in script A, the Date field takes precedence in the ordering of the data, while in script B, Amount is the first ordering field.

Take a moment to think what you would expect as the output of both scripts. You'll discover that the output of each script can be translated to plain English as:

  • In script A, the table is first ordered by Date from oldest to newest and then, for each date, the corresponding records are sorted by Amount in the descending order
  • In script B, the table is first ordered by Amount of the transaction, biggest amounts at the top, and, for records with the same amount, they get ordered by Date from oldest to newest

Normally we will want the table to be sorted by Date first and Amount as a second sorting value. It's important to take this into account when adding it in to our QlikView scripts.

Tip

As a final remark, remember to drop the table on which the Resident load was based if it is no longer needed.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset