SPLITVALUES command

Concept Information

Splitting tables

Splits a table into two or more tables based on unique values in a key field. Output tables use the unique value as the table name, with an optional text prefix.

Note

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

Syntax

SPLITVALUES ON key_field <PREFIX prefix_text> {FIELDS field_name <AS new_field_name> <...n>|FIELDS ALL} <related_table_name.ALL <...n>> <IF test> <TO folder_name> <WHILE test> <FIRST range|NEXT range>

Parameters

Name Description

ON key_field

The key field to use for splitting the table.

The key field can be character, numeric, datetime, or logical.

To use multiple key fields, create a computed field that concatenates the key fields, and use the computed field as the key field.

Note

The key field is automatically included in the output table and does not need to be specified using FIELDS.

PREFIX prefix_text

optional

An alphanumeric string used as a prefix for all output table names.

If you omit PREFIX, output tables use only the unique key field value as the table name.

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.

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.

FIELDS field_name | FIELDS ALL

The fields to include in the output:

  • RECORD use the entire record in the source data file: all fields in the table, and any undefined portions of the record

    Fields are used in the order that they appear in the table layout.

    Preserves computed fields.

  • FIELDS field_name use the specified fields

    Fields are used in the order that you list them.

    To use a field from a related table, specify related_table_name.field_name.

    Converts computed fields to physical fields of the appropriate data type in the destination table – ASCII or Unicode (depending on the edition of Analytics), ACL (the native numeric data type), Datetime, or Logical. Populates the physical fields with the actual computed values.

  • FIELDS ALL use all fields in the table

    Fields are used in the order that they appear in the table layout.

    Converts computed fields to physical fields of the appropriate data type in the destination table – ASCII or Unicode (depending on the edition of Analytics), ACL (the native numeric data type), Datetime, or Logical. Populates the physical fields with the actual computed values.

AS new_field_name

optional

Only valid when using FIELDS field_name.

The new physical name and the display name (alternate column title) for the field in the output table.

Any special characters or spaces that you specify are retained in the display name and automatically converted to underscores ( _ ) in the physical field name in the table layout.

Specify new_field_name as a quoted string. Use a semi-colon (;) between words if you want a line break in the display name.
related_table_name.ALL

optional

Use all fields in the specified related table.

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

TO folder_name

optional

The destination folder for the output tables.

If you omit TO, the tables are saved to the folder containing the Analytics project.

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.

Examples

Split a table based on a single key field

You generate multiple output tables based on unique values in the Vendor_Name field in the Ap_Trans table:

OPEN Ap_Trans
SPLITVALUES ON Vendor_Name PREFIX Vendor_ FIELDS Vendor_No Vendor_City Invoice_No Invoice_Date Invoice_Amount Prodno Quantity Unit_Cost TO "Vendor_tables"

Each output table contains all the records that share the unique key value. The tables are named Vendor_vendor_name. For example, a table named Vendor_Koro_International contains all the records in which Koro International is the Vendor_Name value.

The tables are saved in the Vendor_tables folder in the Analytics working directory.

Split a table based on multiple key fields

You generate multiple output tables based on unique combinations of values in the Vendor_Name and Invoice_Date fields in the Ap_Trans table.

The ALLTRIM( ) function removes unnecessary leading and trailing spaces from the vendor name values. If you do not remove the spaces, they are converted to multiple underscore characters ( _ ), which wastes limited space in the output table names.

The DATE( ) function converts the Invoice_Date field to the character data type, which is required for concatenation.

OPEN Ap_Trans
SPLITVALUES ON ALLTRIM(Vendor_Name)+"_"+DATE(Invoice_Date, "MM/DD/YY") FIELDS Vendor_No Vendor_Name Invoice_No Invoice_Date Invoice_Amount Prodno Quantity Unit_Cost TO "Vendor_Date_tables"

Each output table contains all the records that share a unique combination of key values. The tables are named vendor_name_invoice_date. For example, a table named Koro_International_06_15_23 contains all the records in which Koro International is the Vendor_Name value and 06_15_23 is the Invoice_Date value.

The tables are saved in the Vendor_Date_tables folder in the Analytics working directory.