Sorting records

You can sort records into an ascending or descending sequential order and output the results to a new, physically reordered Analytics table. Outputting to an Analytics table is the only output option.

Sorting records is a prerequisite for several Analytics operations. For more information, see Should I do an explicit sort or use Presort?

Sorting can also be a useful analytical operation in itself, bringing patterns and anomalies into focus.

Note

Indexing records is an alternative to sorting them, and in some situations may be a better choice. For more information, see Should I sort or index?

Should I output the entire record or only specified fields?

When sorting, you have the option of including the entire record in the sorted output table, or only specified fields. There are implications associated with each option, summarized below.

The option you choose can also affect sorting speed. For more information, see How to speed up sorting.

Tip

If you want some of the characteristics of outputting by field, but you need the entire record, output by field and select all fields.

Output type	Implications
Record	The entire record is included in the sorted output table. Computed fields are preserved as computed expressions. Related fields cannot be included. However, the new output table is automatically related to the original child table and you can add fields from the child table to the output table view.
Fields	Only specified fields are included in the sorted output table. Key fields are automatically included and do not need to be specified. Computed fields are converted to physical fields and populated with the actual computed values. Related fields can be included. They become permanent physical fields in the output table. The new output table is no longer related to the original child table.

Sorting on multiple key fields

You can sort records using one key field, or you can create nested sorting schemes by sorting on multiple key fields – primary key field, secondary key field, and so on. Nested sorting supports mixing data types, and mixing ascending and descending order, across key fields.

Example

You want to sort a transaction table in ascending order on a key date field, and within each date in descending order on a key amount field.

The result below illustrates nested sorting that mixes data type (datetime and numeric), and ascending and descending order.

Date field (ascending order)	Amount field (descending order, nested)
15 Jan 2011	$2300.00
15 Jan 2011	$1200.00
15 Jan 2011	$600.00
16 Jan 2011	$900.00
16 Jan 2011	$100.00
17 Jan 2011	$4700.00
17 Jan 2011	$900.00
17 Jan 2011	$500.00

How to speed up sorting

Sorting very large tables, with millions of records, can be time consuming. Sorting requires a significant amount of system resources and can be slowed down if you are performing other tasks simultaneously.

Improve sorting speed

Two options can improve sorting speed:

Output a subset of fields If you need only a portion of the data contained in a record, do not include the entire record in the sorted output table. Select only the fields you need, which in most cases speeds up the sorting process.
The smaller the subset of fields, as a percentage of the total number of fields, the greater the performance improvement.
Increase the memory available for sorting You can allocate a specific amount of memory for sorting, to a maximum of 2000 MB. Go to Tools > Options > Table > Sort Memory, or use the SET SORTMEMORY command.

Additional suggestions

If the amount of time required to sort large tables remains an issue for you, consider:

upgrading your computer hardware
creating a script to sort data on a scheduled basis overnight

Should I do an explicit sort or use Presort?

Sorting records prior to any of the following operations is either a prerequisite, or recommended:

joining tables
merging tables
summarizing (if you want a single group for each set of identical values in the key field)
testing for duplicates
testing for gaps

All these operations include the Presort option, which allows you to incorporate a preliminary sequential sorting of records as part of the operation.

If you are performing two or more of these operations on the same table, explicitly sorting the table first, rather than repeatedly using Presort, may be more efficient, especially if the table contains a large number of records.

Verifying that all source records are in the output table

If you are sorting and outputting all the records in a table, you can set a control total on a numeric field to verify that all the records are in fact output to the new table.

You set a control total for a field in the Table Layout dialog box. Once you have sorted and output the records, in the new table select Tools > Table History to compare the input and output control totals. For more information, see Define a physical field.

Steps

You can sort records by one or more key fields in the active table and output the results to a new Analytics table. You can include the entire record in the sorted output table, or only specified fields.

Show me how

Note

You need free disk space at least 2.5 times the size of the file being sorted for the creation of a temporary file used during the sorting process.

In the Navigator, open the table you want to sort.
Select Data > Sort.
On the Main tab, do one of the following:
- Select the key field(s) from the Sort On list.
- Click Sort On to select the key field(s), or to create an expression.
  Tip
  If you click Sort On, you can optionally specify a descending sort order in the output results for one or more of the key fields by clicking the sort arrow (the default sort order is ascending).
To output entire records, or only specified fields, do one of the following:
- Leave Record selected if you want to include the entire record in the sorted output table.
- Select Fields if you want to include only a subset of fields in the sorted output table.
  Note
  If you need only a portion of the data contained in a record, select Fields, especially if the table is large.
  For more information, see How to speed up sorting.
If you selected Fields, do one of the following:
- Select the appropriate non-key fields from the Other Fields list.
- Click Other Fields to select the non-key field(s), or to create an expression.
Tip
You can Ctrl+click to select multiple non-adjacent fields, and Shift+click to select multiple adjacent fields.
In the To text box, specify the name of the output table.
On the More tab:
1. (Optional) To specify that only a subset of records are processed, select one of the options in the Scope panel.
2. Click OK.

Sort dialog box options

The tables below provide detailed information about the options in the Sort dialog box.

Main tab

Options – Sort dialog box	Description
Sort On	Specifies the key field or fields to use to sort the table. You can select the field or fields from the Sort On list. You can also click Sort On to select the field or fields, to create an expression, or to specify a descending sort order, then click OK. Note Sorting on logical fields requires that Include Filters in Field Lists is selected (Tools > Options > Interface). Key field guidelines: Single key field If you select only one key field, records within each sorted group retain their original sort order relative to one another. Multiple key fields If you select more than one key field, the order in which you select the fields dictates the nested sorting priority. The records are sorted by the first field you select, and if there are multiple occurrences of a value in the first field, the records within the group are then sorted by the second field you select, and so on. For more information, see Sorting on multiple key fields. Key field column order If you include the entire record in the output table, the key field column order in the resulting table is the same as the column order in the source table, regardless of the order in which you select the key fields. If you include a subset of fields in the output table, the key field column order in the resulting table is the order in which you select them. As a group, key fields appear before non-key fields in the output table. Related key field If you want to sort by a field in a child table in a table relation: Click Sort On. The From Table drop-down list in the Selected Fields dialog box allows you to select the appropriate child table. Caution If you sort by a related key field when using the Record option, be aware that the related key field is not included in the sorted output table, which can be confusing.
Record Fields	Specifies whether to include the entire record in the sorted output table, or only a subset of fields. Record includes entire records. The fields in the output table maintain the same order as the source table. Fields includes a selection of individual fields. The fields in the output table appear in the order you select them. If you are including one or more computed fields: select Record to preserve the fields as computed expressions select Fields to convert the fields to physical fields of the appropriate data type and populate them with the actual computed values If you want to include fields from a child table in a table relation: select Fields You cannot include child table fields using the Record option.
Other Fields	If you selected Fields, specifies the non-key fields to include in the sorted output table. You can select the appropriate fields from the Other Fields list. You can also click Other Fields to select the appropriate fields, or to create an expression, then click OK. Note Key fields are automatically included in the output table. They are ignored when specified as Other Fields. As a group, key fields appear before other fields in the output table. If you want to select fields from a child table in a table relation: Click Other Fields. The From Table drop-down list in the Selected Fields dialog box allows you to select the appropriate child table.
If	(Optional) Allows you to create a condition to exclude records from processing. You can enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.
To	Specifies the name and location of the output table. To save the output table to the Analytics project folder enter only the table name. To save the output table in a location other than the project folder specify an absolute or relative file path, or click To and navigate to a different folder. For example: C:\Results\Output.fil or Results\Output.fil. Regardless of where you save the output table, it is added to the open project if it is not already in the project. If Analytics prefills a table name, you can accept the prefilled name, or change it.
Local	If you are connected to a server table, specifies where to save the output table. Local selected saves the output table to the same location as the Analytics project, or to a specified path, or location you navigate to. Local deselected saves the output table to the Prefix folder on AX Server.
Use Output Table	Specifies whether the Analytics table containing the output results opens automatically upon completion of the operation.

More tab

Options – Sort dialog box	Description
Scope panel	Specifies which records in the source table are processed: All (default) all records in the source table are processed. First select this option and enter a number in the text box to start processing at the first record in the source table and include only the specified number of records. Next select this option and enter a number in the text box to start processing at the currently selected record in the source table view and include only the specified number of records. The actual record number in the leftmost column must be selected, not data in the row. While select this option to use a WHILE statement to limit the processing of records in the source table based on criteria. You can enter a condition in the While text box, or click While to create a WHILE statement using the Expression Builder. A WHILE statement allows records to be processed only while the specified condition evaluates to true. You can use the While option in conjunction with the All, First, or Next options. Note The number of records specified in the First or Next options references either the physical or the indexed order of records in a table, and disregards any filtering or quick sorting applied to the view. However, results of analytical operations respect any filtering. If a view is quick sorted, Next behaves like First.
Append To Existing File	Specifies that the output results are appended (added) to the end of an existing Analytics table. The resulting combined table is considered unsorted because the sorted records are appended to the end of the target table, without consideration of any existing sort order in the target table. You can select Append To Existing File if you are certain the records or fields and the target table are identical in structure. You can leave Append To Existing File deselected if you want Analytics to compare the record lengths of the output results and the existing table. If the record lengths are not identical, the data structure is not identical, and the append will not work correctly. Note Leaving Append To Existing File deselected is recommended if you are uncertain whether the output results and the existing table have an identical data structure. For more information about appending and data structure, see Appending results to Analytics tables and text files.
OK	Executes the operation. If the overwrite prompt appears, select the appropriate option. If you are expecting the Append option to appear and it does not, click No to cancel the operation and see Appending results to Analytics tables and text files.

Options – Sort dialog box

Description

Scope panel

Specifies which records in the source table are processed:

All (default) all records in the source table are processed.
First select this option and enter a number in the text box to start processing at the first record in the source table and include only the specified number of records.
Next select this option and enter a number in the text box to start processing at the currently selected record in the source table view and include only the specified number of records.
The actual record number in the leftmost column must be selected, not data in the row.
While select this option to use a WHILE statement to limit the processing of records in the source table based on criteria.
- You can enter a condition in the While text box, or click While to create a WHILE statement using the Expression Builder.
- A WHILE statement allows records to be processed only while the specified condition evaluates to true.
- You can use the While option in conjunction with the All, First, or Next options.

Note

The number of records specified in the First or Next options references either the physical or the indexed order of records in a table, and disregards any filtering or quick sorting applied to the view. However, results of analytical operations respect any filtering.

If a view is quick sorted, Next behaves like First.

Append To Existing File

Specifies that the output results are appended (added) to the end of an existing Analytics table.

The resulting combined table is considered unsorted because the sorted records are appended to the end of the target table, without consideration of any existing sort order in the target table.

You can select Append To Existing File if you are certain the records or fields and the target table are identical in structure.
You can leave Append To Existing File deselected if you want Analytics to compare the record lengths of the output results and the existing table. If the record lengths are not identical, the data structure is not identical, and the append will not work correctly.

Note

Leaving Append To Existing File deselected is recommended if you are uncertain whether the output results and the existing table have an identical data structure.

For more information about appending and data structure, see Appending results to Analytics tables and text files.

Executes the operation.

If the overwrite prompt appears, select the appropriate option.

If you are expecting the Append option to appear and it does not, click No to cancel the operation and see Appending results to Analytics tables and text files.

[ Back to top ]

Analytics 14.1 Help