Split a Field into Multiple Fields - Tableau

Page 1: Introduction to Splitting Fields in Tableau

Purpose

• Splitting string fields containing multiple pieces of information into separate fields.

Example Context

• Example: Customer Name consisting of first and last name.

• Separator identified: space (" ") between first and last name.

Application

• This technique is crucial for better data analysis and visualization.

Page 2: Automated and Custom Splits Options

Menu Options

• Automatic and custom split functionalities are available in Tableau Desktop, but not for web editing.

• These functionalities rely on the SPLIT string function.

Checking SPLIT Support

• To see if data supports the SPLIT function:

  • Check the Data Source page menu for Split and Custom Split options.

  • From the Data pane, check Transform > Split and Custom Split.

Page 3: Splitting Data on the Web

Limitations in Web Authoring

• Web authoring lacks menu support for splitting fields.

Manual Splits in Browser

• Users can manually create a SPLIT calculation.

• String fields can be split automatically based on common detected separators, generating up to ten new fields.

Page 4: Automatically Splitting Fields

Steps for Automatic Split

1. On the Data Source page, click the drop-down arrow next to the field name.

2. Select Split.

3. Alternatively, in the Data pane, right-click the field, and choose Transform > Split.

Result of Automatic Split

• Results in standard calculated fields, editable or deletable like any other.

• Data types of new fields can vary based on detected patterns.

Page 5: Custom Split Options

Custom Split Procedure

• Use Custom Split for more control over how fields are split:

  • Specify the separator and choose split occurrences (first N, last N, or all occurrences).

Automatic Split Limitations

• If automatic splits are slow or no common separator is found, a custom split dialog box appears.

• Helpful when the number of separators varies across values.

Example of Custom Split

• Field: Employee Info (multiple formats with pipe separators).

• Custom configuration: split on "|".

Page 6: Manual Splitting with SPLIT Function

Using SPLIT Function

• SPLIT function mimics custom splits but returns one result field.

• Define separator and value to return via a token number.

Example Calculations

• For Customer Name:

  • First Name: SPLIT([Customer Name], " ", 1)

  • Last Name: SPLIT([Customer Name], " ", 2)

• For Employee Info:

  • Username: SPLIT([Employee Info], | , 1)

  • Department: SPLIT([Employee Info], | , 2)

  • Region: SPLIT([Employee Info], | , 3)

Page 7: Using LEFT and RIGHT Functions

Extracting Specific String Parts

• LEFT and RIGHT functions can extract specific parts of a string based on character counts.

Example Usages

• Given the row value "IGW8892":

  • RIGHT([Value], 4) results in "8892".

  • LEFT([Value], 4) results in "IGW8".

Regular Expressions for Mixed Separators

• For fields with mixed separator types, consider using regular expressions for extraction.

Page 8: Considerations when Splitting Fields

Important Reminders

• New fields cannot be used as keys for joining tables; can be blended.

• Split and custom split not supported for sets, groups, parameters, and bins.

• Maximum of four split fields in Microsoft SQL Server.

Strategy for More Fields

• To exceed ten new fields, repeatedly split a field from a previous split.

Page 9: Troubleshooting Splits

Common Issues

• Split options may be missing if unsupported data source type.

• New fields post-split may contain null values if separations yield no values.

Data Removal Concerns

• Portions of field values may be used as separators, leading to loss of those values.

• Example: Original "Zip-98102" results in "98102" being the only field generated, losing "Zip-" from output.