Sorting or indexing using a computed key field
If the format of the data in a physical key field prevents the accurate sorting or indexing of a table, you may be able to achieve accurate results by creating a computed key field. Examples of data format impeding sorting or indexing include numbers that inconsistently contain non-numeric prefixes or leading blanks, and names that are inconsistently capitalized. You can create a computed key field to make the data consistent, and then sort or index using the computed key field.
You can also sort or index using a computed key field to order tables in ways other than a strictly sequential ordering of key field values. For example, you can use a computed key field to reverse the physical order of records in a table, group even dollar values, or group names that contain a particular string.
Make the computed key field values visible
A best practice when indexing using a computed key field is to add the computed field to the view so that you can see exactly how the computed values are being used to index the table. When you sort using a computed key field, the computed field is automatically included in the new, sorted table.
It is possible to directly incorporate expressions in the sorting and indexing operations, and achieve the same results that you can achieve with a computed key field. However, this approach is not recommended because it hides the computed values that are being used to order the table.
Examples of sorting or indexing using a computed key field
Several examples of sorting or indexing using a computed key field appear below. For comparison, the non-computed, sequential order of the physical key field is also included.
Show me more
|
Description/Computed key field expression |
Analytics Function |
Key field (physical order) |
Key field (sequential order, non-Unicode edition of Analytics) |
Computed key field (sequential order) |
Key field (order based on computed key field order) |
|---|---|---|---|---|---|
|
Ignore non-numeric characters and blanks, sort/index on numeric characters only INCLUDE(dept_ID, "1234567890") |
INCLUDE( ) |
92 12 T-38 20 #85 |
20 #85 12 92 T-38 |
12 20 38 85 92 |
12 20 T-38 #85 92 |
|
Avoid sorting/indexing discrepancies caused by differing upper and lower case UPPER(last_name) |
UPPER( ) |
Smythe JONES Smith JOHNSON SMYTHE Jones SMITH Johnson |
JOHNSON JONES Johnson Jones SMITH SMYTHE Smith Smythe |
JOHNSON JOHNSON JONES JONES SMITH SMITH SMYTHE SMYTHE |
JOHNSON Johnson JONES Jones Smith SMITH Smythe SMYTHE |
|
Invert the physical order of records in a table Note Requires computed key field in descending order. Record numbers always display in ascending order even if a descending order is applied. RECNO( ) |
RECNO( ) |
82.12 87.00 62.79 97.47 43.00 |
43.00 62.79 82.12 87.00 97.47 |
1 2 3 4 5 |
43.00 97.47 62.79 87.00 82.12 |
|
Group even dollar amounts, sequentially order amounts within the group Note Requires a nested sort/index of key field inside computed key field. MOD(trans_amount, 1.00) |
MOD( ) |
82.12 87.00 62.79 97.47 43.00 |
43.00 62.79 82.12 87.00 97.47 |
0.00 0.00 0.12 0.47 0.79 |
43.00 87.00 82.12 97.47 62.79 |
|
Group values containing a particular string, sequentially order values within the group Note Requires computed key field in descending order. Requires a nested sort/index of key field inside computed key field. FIND("Hardware", vendor_name) |
FIND( ) |
Lilydale Hardware |
Binford Tools |
T |
Global Trade Hardware |
|
Triathalon Group |
Bolton Distribution |
T |
Lilydale Hardware |
||
|
Wholesome Hardware |
Global Trade Hardware |
T |
Wholesome Hardware |
||
|
Steel Case Manufacturing |
Industrial Equipment Co-Op |
F |
Binford Tools |
||
|
Industrial Equipment Co-Op |
Lilydale Hardware |
F |
Bolton Distribution |
||
|
Global Trade Hardware |
Steel Case Manufacturing |
F |
Industrial Equipment Co-Op |
||
|
Binford Tools |
Triathalon Group |
F |
Steel Case Manufacturing |
||
|
Bolton Distribution |
Wholesome Hardware |
F |
Triathalon Group |
Steps
The steps for sorting or indexing a table using a computed key field are explained in general terms below.
For detailed information about creating computed fields, and performing sorting or indexing, see the relevant sections of this guide.
- Using an appropriate expression, create a computed key field based on the sort or index physical key field.
- If you are going to perform an indexing operation, add the computed key field to the view.
- Using the computed field as the key field, perform the
regular sorting or indexing procedure.
- If necessary, specify a descending order for the computed key field. Some expressions require a descending order to position results at the top of the table.
- If you want to sequentially order results within groupings created by the computed key field expression, select both the computed key field and the physical key field when sorting or indexing. Make sure to select the computed key field first, so that it takes precedence over the physical key field.
Analytics 14.1 Help