Indexing records
Indexing creates a separate index file (.inx file) that allows access to the records in an Analytics table in a sequential order rather than a physical order (that is, the raw data order).
Indexing does not physically reorder data in tables. However, when a table’s index is active, the data in the view is reordered in accordance with the order specified by the index, and analytical operations process the data based on this order. If a table has more than one view, all views are subject to an active index.
When an index is active, the word Indexed prefaces the record count in the status bar. For example: Indexed Records: 500.
When the index is inactive, the records in a view revert to their original physical order. Upon opening an Analytics table, any existing indexes are inactive by default.
Note
Sorting records is an alternative to indexing them, and in some situations may be a better choice. For more information, see Should I sort or index?
Indexing and field type
You can index any type of field, including computed fields and ad hoc expressions, regardless of data type.
Indexing on logical fields requires that Include Filters in Field Lists is selected (Tools > Options > Interface).
Multiple indexes for a single table
You can create multiple indexes for a single table, and switch between indexes as required, which can be useful when initially assessing a set of data. Only one index can be active at a time.
Nested indexing
You can index records using one key field, or you can create nested indexing schemes by indexing on multiple key fields (primary key field, secondary key field, and so on).
Nested indexing supports mixing ascending and descending order, and mixing data types, across key fields.
Nested indexing with mixed ascending and descending order
You want to see the largest transaction amounts for each day in an unsorted transaction table. You index the table in ascending order on a date key field, and within each day in descending order on an amount key field.
Date field (ascending) |
Amount field (descending, 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 |
Indexing is restricted to Analytics tables
Indexing is restricted to Analytics tables – that is, tables with a .fil source data file. You can index both local and server-based Analytics tables if they have .fil files.
You cannot index database tables that you connect to using a database profile, because there is no .fil file. Data is read directly from the database. To order data in this situation, you can use a SQL ORDER clause in the Data Definition Wizard while accessing the database.
Indexing required for some Analytics commands
Indexing is a prerequisite for using the Find Literal and Seek Expression options in the Search dialog box when searching Analytics tables. (The options are the equivalent of the FIND and SEEK commands.)
These options are available only if:
- a table is indexed
- the index is active
- the index’s primary key field is a character field indexed in ascending order
The table can have a nested index, but only the primary key field is searched.
Conditional indexes
Indexes can include If, First, Next, and While parameters, in which case they become conditional indexes. Only those records that match the condition are indexed, or are displayed or available for analysis when the conditional index is active.
Every time you activate the index the condition is automatically reapplied. You can facilitate certain types of analysis by using conditional indexes to create subsets of larger tables.
When a conditional index with an If parameter is active, the words Filtered Index preface the record count in the status bar. For example: Filtered Index Records: 500. When conditional indexes with First, Next, and While parameters are active, the word Indexed prefaces the record count, like indexes without any conditions.
Indexes and filters
When creating a conditional index with an If parameter or a filter, you can include a global filter (a filter on a view), a local filter (a filter within a command), or both.
The table below provides examples of including filters in indexes, and shows the effect that the indexes have on the sample data.
The filters are part of the indexing syntax, which you can view in the log, or in the index details. For more information, see View index details.
Type of filter |
Description/Indexing syntax |
vendor_ID |
trans_amount |
---|---|---|---|
None |
No index (physical order) |
212 108 359 108 359 212 359 212 359 108 |
1400.00 3400.00 1600.00 1100.00 3400.00 1200.00 2200.00 1700.00 1400.00 2300.00 |
Global |
Index contains only vendor #359 records INDEX ON trans_amount TO "vendor 359 transactions" Global filter: vendor_ID = "359" |
359 359 359 359 |
1400.00 1600.00 2200.00 3400.00 |
Local |
Index contains only transaction amounts $2000 or greater INDEX ON trans_amount IF trans_amount >= 2000 TO "trans amount 2000 or greater" |
359 108 108 359 |
2200.00 2300.00 3400.00 3400.00 |
Global-Local |
Index contains only vendor #359 records with transaction amounts of $2000 or greater INDEX ON trans_amount IF trans_amount >= 2000 TO "vendor 359 transactions 2000 or greater" Global filter: vendor_ID = "359" |
359 359 |
2200.00 3400.00 |
Steps
Index records
You can index records by one or more key fields in the active table, and use the resulting index to temporarily reorder the records without affecting the underlying physical order of the data.
- Select Data > Index.
- On the Main tab, do one of the
following:
- Select the field(s) to index from the Index On list.
- Click Index On to select the field(s), or to create an expression.
If you select more than one field, the order in which you select the fields dictates the nested indexing priority. The records are indexed 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 indexed by the second field you select, and so on. If you do not select additional fields, records within a group retain their original sort order relative to one another.
For information about indexing using expressions and computed fields, see Sorting or indexing using a computed key field.
Note
The combined length of the fields being indexed cannot exceed 247 characters.
- If you clicked Index On, you can optionally specify a descending index order for one or more selected fields by clicking the sort arrow (the default is ascending).
-
If there are records in the current view that you want to exclude from processing, enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.
Note
The If condition is evaluated against only the records remaining in a table after any scope options have been applied (First, Next, While).
The IF statement considers all records in the view and filters out those that do not meet the specified condition.
- Do one of the following:
- In the To text box, specify the name of the index file.
- Click To and specify the index file name, or select an existing index file in the Save or Save File As dialog box to overwrite the file.
If Analytics prefills an index file name, you can accept the prefilled name, or change it.
Note
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.
Tip
A best practice is to give indexes meaningful names that describe the nature of the ordering imposed by the index. For example, “Date_Amount_D” could be the name of an index that orders a table by Date in ascending order, and within each day by Amount in descending order.
- Select or deselect Use Output Table depending
on whether or not you want to activate the index immediately.
You can activate a table’s index at any time by selecting it from the Index drop-down list at the top right of the view.
- Click the More tab.
-
Select the appropriate option in the Scope panel:
- All – This option is selected by default. Leave it selected to specify that all records in the view are processed.
- First – Select this option and enter a number in the text box to start processing at the first record in the view 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 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 view based on a particular criterion or set of 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 in the view to be processed only while the specified condition evaluates to true. As soon as the condition evaluates to false, the processing terminates, and no further records are considered. You can use the While option in conjunction with the All, First, or Next options. Record processing stops as soon as one limit is reached.
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.
- Click OK.
- If the overwrite prompt appears, select the appropriate
option.
An entry for the index is added to the Index drop-down list in the View tab. If you selected Use Output Table, the index is activated and the table is sorted according to the index.
Activate or deactivate indexes
You can activate an index at the time you create it, or at any time after creating it. Upon opening an Analytics table, any existing indexes are inactive by default.
- To activate an index, do one of the
following:
- When creating an index, select Use Output Table in the Index dialog box to activate the index right away.
- Select the index from the Index drop-down list at the top right of the view.
- To deactivate an index, do one of the following:
- Select (None) in the Index drop-down list at the top right of the view.
- Switch to another index.
- Close the table.
View index details
You can view the details of an index – that is, the actual syntax of the specific Index command. The command syntax includes the key field(s), and any parameters, filters, or expressions. Index details reveal exactly how a particular index is processing the records in a table.
- Open the table containing the index.
- Right-click the table in the Navigator and select Properties.
- Click the Indexes tab, select
the index name, and click Details.
The Index Properties dialog box displays the index details:
- Command displays the syntax of the specific Index command, including any local filter.
- Filter displays the syntax of any global filter that is part of the index.
- Click OK and OK again to exit the Table Properties dialog box.
Maintain indexes
You can copy, rename, or delete an index in the Indexes tab of the Table Properties dialog box. You can also add additional indexes from the same location.
Note
You can perform these maintenance tasks only through Analytics. If you directly rename an index file (.inx file) in a Windows folder the index file is automatically recreated with the original name the next time you activate the index in Analytics. If you directly delete an index file, the index file is automatically recreated the next time you activate the index.
- Open the table containing the index.
- Right-click the table in the Navigator and select Properties.
- Click the Indexes tab, select
the index name, and do one of the following:
- Click Copy to copy the
index.
The index is copied with an incrementing number added to the end of the index name.
- Click Rename, enter a new name,
and click OK to rename the index.
Note
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.
- Click Delete, then click Delete again to delete the index.
- Click Copy to copy the
index.
- If you want to add a new index, click Add.
The Index dialog box appears, allowing you to create an index in the usual manner.
- Click OK to exit the Table Properties dialog box.