Databases Reference
In-Depth Information
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.
As a final remark, remember to drop the table on which the
Resident
load was based if it is no longer needed.
The Peek function
Another tool we'll add to our collection in this set of data transformation techniques
is the
Peek
function. The
Peek
function is an inter-record function that allows us to
literally peek into previously-read records of a table and use its values
to evaluate a condition or to affect the active record (the one being read).
The function takes one mandatory parameter, the field name into which we will
"peek", and two optional parameters, a row reference and the table in which the
field is located.
For example, an expression like:
Peek('Date', -2)
