When you are working with text, it is common to compare one value against another. Because comparison is case-sensitive, text stored in various case formats can be challenging to compare. Analytics provides functions that make comparison more reliable by converting the text you are comparing to normalized case formats.

When scripting in Analytics, you can use two operators to compare text values:

  • equality operator (=) evaluates to true if the value on the left-hand side of the equality operator is exactly the same as the value on the right-hand side
  • inequality operator (<>) evaluates to true if the value on the left-hand side of the inequality operator is not exactly the same as the value on the right-hand side

Both operators are case-sensitive and compare two values: valueOne <> valueTwo.

You are working with the following table and you need to filter it so that only records for the Finance department are shown:

Department Max_Hourly Min_Hourly Position
Executive 205.13 166.67 CEO & President
Executive 141.03 89.74 VP, Finance
Finance 24.62 20.51 Accountant
finance 23.08 17.95 Clerk, Cash Disbursements
finance 18.46 14.67 Clerk, Payables
Finance 18.46 14.67 Clerk, Purchasing
Information Systems 23.08 14.36 Technical Support
Information Systems 30.77 23.08 Web Administrator

To filter the table, you create a simple expression using the equality operator (=):

COMMENT filters the table to show records where Department is "Finance"
SET FILTER TO Department = "Finance"

First filter results

Based on this filter, you expect to see four records in the filtered table, but instead you only see two:

Department Max_Hourly Min_Hourly Position
Finance 24.62 20.51 Accountant
Finance 18.46 14.67 Clerk, Purchasing

Because the equality operator is case-sensitive, records where the Department field contains "finance" are excluded from the results. You need to include these records in the results as well.

To help you perform comparisons, Analytics provides functions that make comparison more reliable by converting the text you are comparing to a known case format, such as lowercase.

To filter the table so that your results include all employees from the Finance department, regardless of case format, you use the same expression but you use the LOWER( ) function to convert all the values to lowercase:

COMMENT filters the table to show records where Department is "finance"
SET FILTER TO LOWER(Department) = "finance"

When the expression is evaluated, LOWER("Finance") becomes "finance" and is then compared to the string on the right-hand side of the equality operator.

Second filter results

When using the LOWER( ) function in the expression, the filter includes all employees from the Finance department:

Department Max_Hourly Min_Hourly Position
Finance 24.62 20.51 Accountant
finance 23.08 17.95 Clerk, Cash Disbursements
finance 18.46 14.67 Clerk, Payables
Finance 18.46 14.67 Clerk, Purchasing