反馈已提交

网络繁忙

Splitting Rows and Columns

  • Last update:  2024-03-27
  • Overview

    Version

    FineBI Version
    Functional Change
    V6.0.6

    /

    Application Scenario

    When you edit data, you can click Split Field to split strings according to your needs.

    Function Description

    You can split by the separator or by the character quantity.

    Split by the separator: USA-California-Sacramento can be split into three field values: USA, California, and Sacramento using - as the separator.

    Split by the character number: MYY001 has a total of six characters. It can be split into two field values with three characters: MYY and 001.

    Function Page Overview

    1. Choose More > Split Field on the data editing page to go to the function page.

    2. The function page is shown in the following figure.

    Settings
    OptionsDescription

    Select Field

    The text type field of the current table.

    Select the field to split.

    Split Method

    By Separator

    Example: USA-California-Sacramento can be split into USA, California, and Sacramento using - as the separator.

    By Character Number

    Example: MYY001 has a total of six characters. It can be split into two field values with three characters: MYY and 001.

    Split to

    Column

    Split one field value into multiple columns.

     

    Row

    iconNote:

    1. This function does not support splitting by character number.

    2. This function is not supported in the direct connection.

    Split one field value into multiple rows.

     

    Split Result

    Number N Column

    Select one field to keep.

    Top N Column

    Select the top N fields to keep.

    Example

    You can download the example: Spilt Rows and Columns.xlsx.

    Split Through the Separator

    iconNote:
    Oracle does not support the split_part function. Therefore, Oracle tables do not support splitting columns by the separator in the directly connected mode.

    1. To extract the states from the Targeted State string, split the states by using as the separator, select the Number N Column for Split Result, and enter 1 on the right side.

    2. To preserve the states and cities after splitting Targeted State, select Top N Column for Split Result, and enter 2 on the right side. Both fields can be kept and displayed after splitting.

    Delete the original field and name the latter split field City through Field Settings.

    3. To split City into columns with only one city in each column, split the cities by ,, select Top N Column for Split Result, and enter on the right side because the field with the most values contains four cities.

    However, you may feel that splitting into columns is inconvenient for further analysis and calculations. It would be better if the field could be split into rows.

    4. Split City into rows.

    Select City for Select Field, By Separator for Split Method, and , as the separator.

    Split Through the Character Quantity

    iconNote:
    Splitting by the character quantity does not support splitting to rows.

    The Staff ID was a string, which made it inconvenient to sort and compare by value. In this case, you can extract the numeric part of Staff ID.

    1. Select By Character Quantity for Split Method, with a character number of 3.

    2. Modify the field of the numeric part to Value for more convenient comparison and sorting.

    附件列表


    主题: Adding and Editing Data
    • Helpful
    • Not helpful
    • Only read

    feedback

    鼠标选中内容,快速反馈问题

    鼠标选中存在疑惑的内容,即可快速反馈问题,我们将会跟进处理。

    不再提示

    10s后关闭