Databases Reference
In-Depth Information
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