INDEX command

Concept Information

Creates an index for an Analytics table that allows access to the records in a sequential order rather than a physical order.

Syntax

INDEX {<ON> key_field <D> <...n>|<ON> ALL <EXCLUDE field_name <...n>>} TO file_name <IF test> <WHILE test> <FIRST range|NEXT range> <OPEN> <ISOLOCALE locale_code>

Parameters

Name	Description
ON key_field D <...n> \| ON ALL	The key field or fields, or the expression, to use for indexing. You can index by any type of field, including computed fields and ad hoc expressions, regardless of data type. ON key_field use the specified field or fields If you index by more than one field, you create nested indexing in the table. The order of nesting follows the order in which you specify the fields. Include D to index the key field in descending order. The default index order is ascending. ON ALL use all fields in the table If you index by all the fields in a table you create nested indexing. The order of nesting follows the order in which the fields appear in the table layout. An ascending index order is the only option for ON ALL.
EXCLUDE field_name optional	Only valid when indexing using ON ALL. The field or fields to exclude from the command. EXCLUDE allows you to fine-tune ON ALL, by excluding the specified fields. EXCLUDE must immediately follow ON ALL. For example: ON ALL EXCLUDE field_1 field_2
TO file_name	The name of the index and the associated index file. The index file is created with an .INX extension. Note In the Analytics user interface, index names are limited to 64 alphanumeric characters. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.
IF test optional	A conditional expression that must be true in order to process each record. The command is executed on only those records that satisfy the condition. Note The IF parameter is evaluated against only the records remaining in a table after any scope parameters have been applied (WHILE, FIRST, NEXT).
WHILE test optional	A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached. Note If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached.
FIRST range \| NEXT range optional	The number of records to process: FIRST start processing from the first record until the specified number of records is reached NEXT start processing from the currently selected record until the specified number of records is reached Use range to specify the number of records to process. If you omit FIRST and NEXT, all records are processed by default.
OPEN optional	Open the table and apply the index to the table.
ISOLOCALE locale_code optional	Note Applicable in the Unicode edition of Analytics only. The system locale in the format language_country. For example, to use Canadian French, enter fr_ca. Use the following codes: language ISO 639 standard language code country ISO 3166 standard country code If you do not specify a country code, the default country for the language is used. If you do not use ISOLOCALE, the default system locale is used.

Examples

Create an index and open the table

In the Vendor table, you create an index on the Vendor City field and open the table:

OPEN Vendor
INDEX ON Vendor_City to "CityIndex" OPEN

Create an index and apply it to a table

In the Vendor table, you create an index on the Vendor City field. Later, you apply the index to the table:

OPEN Vendor
INDEX ON Vendor_City to "CityIndex"
.
.
.
SET INDEX TO "CityIndex"

Remarks

For more information about how this command works, see Indexing records.

The sort sequence used by the INDEX command

The INDEX command uses whatever sort sequence is specified in the Sort Order option (Tools > Options > Table). The default sort sequences are shown below.

For detailed information, see The Sort Order option and sort sequences .

Analytics Edition	Sort Order default	Associated sort sequence
non-Unicode	System Default (ASCII)	Numbers, then uppercase, then lowercase: 0, 1, 2... A, B, C... a, b, c... For example, “Z” sorts before “a”.
Unicode	Mix Languages (UCA) (Unicode collation algorithm)	Numbers, then lowercase and uppercase intermixed: 0, 1, 2... a, A, b, B, c, C... For example, “a” sorts before “Z”.

Analytics Edition

Sort Order default

Associated sort sequence

non-Unicode

System Default

(ASCII)

Numbers, then uppercase, then lowercase:

0, 1, 2... A, B, C... a, b, c...

For example, “Z” sorts before “a”.

Unicode

Mix Languages (UCA)

(Unicode collation algorithm)

Numbers, then lowercase and uppercase intermixed:

0, 1, 2... a, A, b, B, c, C...

For example, “a” sorts before “Z”.

Case sensitivity

INDEX is case sensitive. Depending on which edition of Analytics you are using (non-Unicode or Unicode), casing in strings may affect indexing.

You can use the UPPER( ) function in conjunction with INDEX if you do not want case to affect indexing:

INDEX ON UPPER(key_field) TO "Index_file"