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:
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.
Qlik View Related Interview Questions
|Microstrategy Interview Questions||IBM Cognos Interview Questions|
|PL/SQL Interview Questions||MSBI Interview Questions|
|VBA For Excel Interview Questions||SAP BO Interview Questions|
|SQL Database Interview Questions||Qlik View Interview Questions|
|R Programming language Interview Questions||Pentaho Interview Questions|
|Advanced SAS Interview Questions|
Qlik View Tutorial
Seeing Is Believing
Data Modeling Best Practices
Basic Data Transformation
Set Analysis And Point In Time Reporting
Advanced Data Transformation
More On Visual Design And User Experience
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.