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.
|
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:
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:
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”. |
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"