Splitting tables

Concept Information

SPLITVALUES command

You can split an Analytics tables into two or more separate tables based on unique values in a key field. For example, you could split a table containing data for all company branch locations into separate branch tables based on the values in the Branch field.

The separate tables are new output tables. Each output table contains all the records that share the unique key value. The original table is not changed in any way.

Note

The maximum number of unique key field values supported when splitting a table is 450.

How output tables are named

Output tables use the unique key field value as the table name, with an optional text prefix. Special characters and spaces in key field values are replaced with underscores ( _ ) in table names.

Using more than one key field

To split a table based on unique combinations of values in multiple fields:

  • Create a computed field that concatenates the fields containing the values that you want to use.

  • Use the computed field as the key field.

Key values are not case sensitive

When the key value output tables are generated, the case of individual key values is not considered. For example, Koro International and KORO International are treated as identical values and contained in the same output table.

The table splitting process involves sorting key field values, and the first-sorted value in each group of values is used for the output table name. Uppercase is sorted before lowercase, so in the example above the output table name would be KORO_International, even if KORO International is located below Koro International in the source table.

Steps

Split a table into two or more separate tables based on unique values in a key field.

Specify the key field to use for splitting the table

  1. In the Navigator, open the table that you want to split into separate tables.

  2. On the Analytics main menu, select Data > Split Values.

  3. On the Main tab in the Split Values dialog box, do one of the following:

    • Select the key field from the Split Values On dropdown list.

    • Click Split Values On to select the key field, or to create an expression.

      Click Split Values On if you want to select the key field from a child table in a table relation. The From Table dropdown list allows you to select the appropriate child table.

Specify a table name prefix (optional)

In the Table Name Prefix field, enter a prefix to add to the beginning of each output table name.

If you leave the Table Name Prefix field blank, output tables use only the unique key field value as the table name.

Tip

If you use a datetime or numeric key field, use a prefix such as D_ or N_ to ensure that the first number in the key values is retained in the output table names.

Note

The table name prefix is limited to 32 alphanumeric characters. Overall table names are limited to 64 alphanumeric characters. If the combination of a table name prefix and a key field value exceeds 64 characters, the table name is truncated from the right.

The table name prefix can include the underscore character ( _ ), but no other special characters, or any spaces. The prefix cannot start with a number.

Specify the fields to include in the output tables

  1. Select one of the following:

    • View all the fields in the current view are included in the output tables.

      The fields are included in the order that they appear in the view. The key field is automatically included in the leftmost position in the output tables.

    • Fields lets you select which fields are included in the output tables.

      The fields are included in the order that you select them. The key field is automatically included in the leftmost position in the output tables. You do not need to specifically select it.

  2. If you selected Fields, do one of the following:

    Select the appropriate fields from the List Fields list.

    Click List Fields to select the appropriate fields, or to create an expression, then click OK.

    Click List Fields if you want to select fields from a child table in a table relation. The From Table dropdown list allows you to select the appropriate child table.

Finalize the settings

  1. 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).

  2. Optional. Do one of the following:

    • In the To Folder text box, specify the name or path of an existing Windows folder to contain the output tables.

    • Click To Folder and select an existing Windows folder in the Browse for Folder dialog box.

    If you leave the To Folder field blank, the tables are saved to the folder containing the Analytics project.

  3. Click the More tab.

  4. In the Scope panel, select the appropriate option:

    • All
    • First
    • Next
    • While
    Scope option Details
    All (Default) Specifies that all records in the view are processed.
    First Enter a number in the text box. Starts processing at the first record in the view and includes only the specified number of records.
    Next Enter a number in the text box. Starts processing at the currently selected record in the view and includes only the specified number of records. The actual record number must be selected in the leftmost column in the view, not data in the row.
    While

    Use a WHILE statement to limit the processing of records in the view based on a particular criterion or set of criteria.

    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 First or Next options reference either the physical order or the indexed order of records in a table. First or Next disregard any filtering or quick sorting applied to a table view. However, output results of analytical operations respect any filtering.

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

Generate the output tables

  1. Click OK.

    The output tables are generated based on unique values in the key field that you specified.

    If one or more tables with an identical name already exist, the overwrite prompt appears.

  2. If the overwrite prompt appears, select the appropriate option:

    • Yes All existing tables with identical names are overwritten by new tables.

    • No All existing tables with identical names are preserved. New tables are given a numeric suffix to make their names unique.

    • Cancel Cancel the operation. No new tables are generated.

    Note

    The overwrite prompt appears only once. The choice you make applies to all existing and new tables.