Performing calculations on text

Follow
In BIME's calculation engine you have a wide range of options for cutting, finding, editing, creating, and analyzing text. In addition to the functions below, you can use the basic operator + to join text.
Note: Joining text will run results together unless you insert spacing in "double quotes". For example [Category] + "" + [Subcategory]. Similarly, all text you are adding to or searching within results, must be in "double quotes".

This article contains the following sections:

Available text functions

Below are all available text functions in BIME. In the Functions window, you can view only text functions by clicking the Filter button, then selecting the Text category.

Table 1. Text functions
FunctionDefinition
CONTAINS(Text, Text_to_search) Returns TRUE if the first entered parameter contains the second as a substring.
STARTSWITH(Text,Text_to_search) Returns TRUE if the first entered parameter starts with the second.
ENDSWITH(Text, Text_to_search) Returns TRUE if the first entered parameter ends with the second.
LEFTPART(Text,Number) Returns the left-most number of characters in the entered text.
RIGHTPART(Text,Number) Returns the right-most number of characters in the entered text.
SUBSTR(Text,Number_start_index,Number_end_index) Returns a text consisting of the character specified by start index (the first entered integer) and all characters up to end index-1 (the second entered integer).
FIND(Text, Text_to_find, Number_start_index) Returns the index, in the first entered text, of the first instance of the second entered text, from the entered start index. Returns -1 if the second entered text is not found. Note: The index of the first character of a substring is 0.
LENGTH(Text) Returns the length of the entered text.
REPLACE(Text, Text_to_replace,Text_to_replace_with) Returns a copy of the first entered text in which all instances of the second entered text have been replaced with the third.
LOWERCASE(Text) Returns the lower-case version of the entered text.
UPPERCASE(Text) Returns the upper-case version of the entered text.
LTRIM(Text) Returns a copy of the entered text in which all left-white spaces have been removed.
RTRIM(Text) Returns a copy of the entered text in which all right-white spaces have been removed.
TRIM(Text) Returns a copy of the entered text in which all left and right-white spaces have been removed.
LPAD(Text,length,pad) Pads text on the left and repeats until the resulting string is exactly the entered character length.
RPAD(Text,length, pad) Pads text on the right and repeats until the resulting string is exactly the entered character length.
SENTIMENT_ANALYSIS(_text) Returns 'Positive' or 'Negative' based on analysis of the text.
IS_POSITIVE(text) Returns 'true' for positive and 'false' for negative based on analysis of the text.
STRING(number) Converts the entered number to text.
REGEXP_MATCH(text, regexp) Returns true if the entered text matches the entered regular expression.
REGEXP_EXTRACT(text, regexp) Returns the portion of the entered text that matches the capturing group within the entered regular expression
REGEXP_REPLACE(text, regexp, replace text) Returns a string where any substring of the entered text that matches the entered substring is replaced by the entered replacement text. For example, REGEXP_REPLACE("Hello","lo", "p"_ returns Help.

Writing formulas with text functions

This section will provide an example of how you can use text functions in your reports. The example also uses the IF THEN ELSE function to create a conditional expression (see Using the IF THEN ELSE function).

This section discusses the following use case:

Filtering values with text functions

The CONTAINS function provides an easy way to filter results by values containing a specific text. The example below uses CONTAINS to limit results to values in the Group attribute with "Support" included in the value label.

To limit results to values containing specific text
  1. In the Calculation engine menu, select Standard calculated attribute.
  2. Name your calculated attribute. This example uses Support groups.
  3. Click the +Add button under Functions.
  4. Select the IF THEN ELSE function.
  5. Click inside the _boolean_condition parentheses.
  6. Click the +Add button under Functions.
  7. Select the CONTAINS function.

  8. In the _text parameter, select your attribute containing the results you would like to filter by. This example uses Group.
  9. In _text_to_search, enter the text to use as your filter. All text must be entered in "double quotes". This example uses "Support".

  10. For the _value_if_true select your _text parameter attribute from the Select a field drop-down list.
  11. Delete ELSE and value_if_false.

  12. Click Save.
  13. Click the + button on Columns, Rows, or Explosions to add your calculated attribute to your query
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk