3 Replies Latest reply on Jul 19, 2016 11:44 AM by Joe Oppelt

    If/Then Booleans and Strings

    Peter Seok

      Hi Everyone,

       

      I asked a question earlier about a formula that would convert the last 3 characters of an alphanumeric code I have to a number, and I got a good answer from one of the members who gave me the following formula:

       

      IF INT(RIGHT([Code],3)) > 299 AND INT(RIGHT([Code],3)) < 999 THEN "Exclude" ELSE "Include" END

       

      However, there are a few more stipulations that I need to add onto this, (just FYI, I'm filtering right at the data source, so it looks like I can't use the same field more than once in my direct filters from my MS SQL server):

       

      In the case of my Code ending in a letter, most notably 'a' or 'b', it breaks the formula above, as it doesn't allow conversion to an integer, so I need to combine this with the clause above.  I tried this:

       

      IF CONTAINS(RIGHT([Code],3),"a") OR IF CONTAINS(RIGHT([Code],3),"b") OR IF INT(RIGHT([Code],3)) > 299 AND INT(RIGHT([Code],3)) < 999 THEN "Exclude" ELSE "Include" END

       

      It won't take the statement, any help would be greatly appreciated, thanks very much!

        • 1. Re: If/Then Booleans and Strings
          Joe Oppelt

          When you say you are filtering "right at the data source", do you mean you have added a data source filter?  And are you using it on the "condition" tab of the filter.

           

          Usually when I create a calc that results in "this" or "that", I put that calc on the filter shelf and select for whichever value I need for that sheet.

           

          But when I create a data source filter, I result in a Boolean value and put it on the "condition".

           

          The attached superstore example has a data source filter using the condition tab.  It grabs two specific values.  See attached.  (Vrersion 8.2 workbook.)

          • 2. Re: If/Then Booleans and Strings
            Peter Seok

            Hi Joe,

             

            Thanks for coming back so quickly, yes, I'm trying to isolate my population down by using the condition tab on the data source filter.  I don't want to use the filter shelf on a worksheet, because I'd like to isolate the correct population down to begin with.  I think I can mimic what you have in your example and reflect what I need in terms of criteria, but I'm still looking for an operator that basically denotes 'does not contain', that I can write into my formula.

            • 3. Re: If/Then Booleans and Strings
              Joe Oppelt

              Try:  NOT CONTAINS(...

               

              In the attached, edit the data source filter.  Un-comment the last line.  You will see that it will take out "South America" from the list it returns.