Overview
Merging and splitting of fields are essential to data integration.
In datasets, some fields need to be merged.
Some fields merged by separators need to be split into multiple columns or be split to extract characters at a specified position.
Field Merging
Merging Directly
Text fields can be merged through + or the CONCATENATE function.
Note:Take merging of City and Province by + as an example.
Enter the formula City+Province, to merge these two columns, as shown in the following figure.
You can also use the CONCATENATE function, as shown in the following figure.
Merging by Separators
+ can also be used to merge fields by separators.
For example, to merge City and Province in the Demo data BIDemo_Urban Area Dimension, and connect two fields with -, add a formula column, enter the formula City+"-"+Province, and click OK, as shown in the following figure.
Wait until the result is displayed, as shown in the following figure.
Field-based Data Splitting
More columns can be added based on characters in a field.
In FineBI, you can use the function Formula Column or Calculation Indicator in the dashboard to add more columns based on a field
Example
For example, you can follow the steps below to add a formula column that contains the last 12 digits of the Client ID field in the self-service dataset.
1. Create a self-service dataset with the Demo data Contract Fact. Click Field Settings, and select Contract ID and Client ID.
2. Click Formula Column and enter the formula RIGHT(Client ID,12). The added column contains the last 12 digits of Client ID, as shown in the following figure.
More Formulas
The following table introduces more formulas for field splitting.
| Requirement | Formula | |
|---|---|---|
| Fields with only one separator | Extract a specified number of characters. | Example: LEFT(Express line,7)
|
Extract the first several characters. | LEFT(Field Name,FIND("separator",Field Name)-1) Example: LEFT(Express line,FIND("-",Express line)-1)
| |
Extract the last several characters. | RIGHT(Field Name,LEN(Field Name)-FIND("separator",Field Name)) Example: RIGHT(Express line,LEN(Express line)-FIND("-",Express line))
| |
Remove a specified number of the last several characters. | LEFT(Field Name,LEN(Field Name)-number of characters to be removed) Example: LEFT(Payment Type,LEN(Payment Type)-7)
| |
Remove a specified number of the first several characters. | RIGHT(Field Name,LEN(Field Name)-number of characters to be removed) | |
Fields with several separators | Extract characters after the last separator. | INDEXOF(SPLIT(Field Name,"separator"),LEN(SPLIT(Field Name,"separator"))-1) Example: INDEXOF(SPLIT(route,"-"),LEN(SPLIT(route,"-"))-1)
|
Extract characters after the penultimate separator. | INDEXOF(SPLIT(Field Name,"separator"),LEN(SPLIT(Field Name,"separator"))-2) Example: INDEXOF(SPLIT(route,"-"),LEN(SPLIT(route,"-"))-2)
| |
By following the previous steps, you can add more columns based on fields in various ways. | ||
