Counting the Number of Occurrences of a Character in a Field

1.1 Expected view

Count the number of occurrences of the same character in the field. For example, count the number of occurrences of "/" in the "Account" field. As shown below:

1.2 Implementation ideas

1) After uploading the data, add the fields to be analyzed in the self-service dataset.

2) Then add a formula in the newly added column, and use the nested formula of the SUBSTITUTE function and the LEN function to achieve the effect.

3) After saving the self-service dataset, update the data to view the effect.

Sample usage data: count the number of same characters.rar

2.1 Upload data

Add "EXCEL Dataset" under "Package" in "Data Preparation", upload the original data, as shown below:

After uploading the "User Authority Table", modify the table name to "Count the same characters", modify the field type as needed, and click "OK" to finish uploading, as shown below:

2.2 Create a new self-service dataset

After uploading the data, continue to add the "self-service dataset" under the "package" to analyze and process the data, as shown below:

2.3 Selection field

Add a table name to the newly created self-service dataset, and then select all the fields in the uploaded EXCEL table "Count the same characters" and add them to the dataset, as shown in the following figure:

2.4 New column

1) Add "New Column", as shown in the figure below:

2) Add a new column to count the number of "/" in the "Account" field. Enter the formula:

 LEN(account)-LEN(SUBSTITUTE(account,"/","")), where the "account" field needs to be added in the text field on the left to be effective. Click "OK" when finished, as shown below:

The formula description is shown in the following table:

After adding the "Count the number of '/' " column, click "Save" to complete the data processing, as shown below:

Click "Update Data" to update the created self-service dataset, as shown in the figure below:

For details of the effect, please refer to section 1.1 of this article.