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).
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:
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 orderB
, 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 newestNormally 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.