Data Merging/Truncation/Splitting

1. Overview

When performing data merging, the splicing and merging of fields and the split of columns are indispensable.

Sometimes it is necessary to merge some of the required fields in the dataset.

In the dataset, the field structure is a string connected by certain delimiters. When using it, it needs to be split to extract only the string at a fixed position, or the whole is split into multiple columns.

2.1 Fields are merged directly

In versions after 5.1.12, the merging of text fields is achieved through "+", of course, the function CONCATENATE can also be used.

Note: similar to & in Excel.

For example, the last name and first name need to be merged into a field "name", as shown in the figure below:

Enter the formula: First name+Last name, then the fields are merged, as shown in the following figure:

You can also use the CONCATENATE function, as shown in the figure below:

2.2 Merge fields and separate them with one character

If you want to add a character to separate each field, you can also use "+".

For example, to use the data in "package>FRDemo_map_WorldMa", you need to merge the "Continent", "Country", and "Capital" data and separate them with "-".

Create a field, name the field and enter the formula: Continent + "-" + Country + "-" + Capital, click "OK", as shown below:

The resulting "Region" field is shown in the figure below:

3. Split fields

According to the characters in the field, split the field into multiple columns.

In FineBI, you can use functions to create "new column" or add "calculation indicator" to the dashboard to achieve field split.

3.1 Example

For example, you need to intercept phone numbers. For example, create a new field in the self-service dataset for sorting, and intercept the last eight digits of the "Home telephone" to construct a new field "phone number".

1) Use Demo data "FRDemo_Employe_copy" to make a self-service dataset, select the fields "Full Name" and "Home telephone", as shown in the figure below:

2) Select the new column, use the RIGHT function, add the new column "phone number" to intercept the last eight digits of the phone number, and enter the formula: RIGHT(Home telephone, 8), as shown in the figure below:

3.2 More formulas

See the table below for more breakdown formulas.

After sorting, you may need to merge multiple split fields into one column. At this time, you can use the upper and lower merge function. For details, see: Inverse Perspective.