Hello everyone, I've been trying to put a filter on Role_ID to exclude any role that begins with AP or PR. I've tried using the <> 'AP' and not in functions, but keep getting errors. any suggestions? 

Hi Sylvia

What error messages do you get?  What do you understand them to mean?  Can you clarify the context in which you are attempting the filter please.

An expression which uses an operator such as <> and a operand such as 'AP' will ask for an exact match of the string which is in the quotes.   The same would be true for the not in operator.

 I think you might want to look at adding the substring function to your expression.   In your case you know the position and length of the substring which you are looking for and both are not variable, which simplifies the problem a bit.

For example, this expression will filter out any country whose name begins with 'Un'.

substring (COUNTRY_EN, 1,2) <> 'Un'.   

This would exclude the UK and the US.

 In your case you might want to think about the not in operator.

 substring (COUNTRY_EN, 1,2) not in ( 'Un','Sw')

This would exclude the UK,the US, Sweden, and Switzerland.

Hope that helps.

It looks like you were already provided with a method using the SUBSTRING operator to target only certain characters in the string if you know exactly where they are in each value.  Another method is the LIKE operator where you give an expression in your filter like this:  [Role_ID] NOT LIKE 'AP%' OR [Role_ID] NOT LIKE 'PR%'

The LIKE (and NOT LIKE) operator requires you to define where it's allowed to find any number/type of characters before the string you are looking for and/or after the string you are looking for via the wildcard symbol (%).   Here's some helpful tips on where to place the wildcard based in various scenarios.  

• If you only wanted to exclude values that start with "PR" and have any number of characters after "PR", even none, then you'd place the wildcard (%) after "PR" like this:  [Role_ID] NOT LIKE 'PR%'
• If you only wanted to exclude values that end with "PR" and have any number of characters before "PR", even none, then this:  [Role_ID] NOT LIKE '%PR'
• If you wanted to exclude values that have "PR" anywhere in the string as long as "PR" is found somewhere in it, then this:  [Role_ID] NOT LIKE '%PR%'

------------------------------
Logan Whitaker
------------------------------