9 Replies Latest reply on Jun 29, 2016 9:03 AM by paolo.raia

    Nested IF help

    paolo.raia

      Hi there,

       

      I have the following IF statement which Tableau cannot compute.  Obviously, I have an incorrect syntax somewhere:-

       

      IF LEFT([Title],4) = "ABC:" THEN

         IF LEN((REPLACE(TRIM(MID([Title],1,FIND([Title],"-",1))),"ABC:","")))=0 THEN

         "***Cannot evaluate TITLE due to missing single dash '-'"

         ELSE

         REPLACE(TRIM(MID([Title],1,FIND([Title],"-",1))),"ABC:","")

         END

           ELSEIF LEN((REPLACE(TRIM(MID([Title],1,FIND([Title],"—",1))),"ABC:","")))=0 THEN

           "***Cannot evaluate TITLE due to missing single dash '-'"

           ELSE

           REPLACE(TRIM(MID([Title],1,FIND([Title],"—",1))),"ABC:","")

         END

      ELSE "***Cannot evaluate TITLE due to non 'ABC:'"

      END

       

      Example of text to evaluate: "ABC: Tableau Inc - Please supply details of problem".

       

      So what I am trying to do, is if the text begins with "ABC:", I want the result to return "Tableau Inc -".

       

      In between this, it must also evaluate the text to see if there is a single dash or double-dash (em).

       

      I've got this working with just the single dash evaluation, but cannot nest the second double-dash.

       

      Many thanks in advance.

       

      Regards.

       

       

       

        • 1. Re: Nested IF help
          Tharashasank Davuluru

          Hi,

           

          You can try using REGEXP functions in tableau which will make your work easier.

          • REGEXP_REPLACE(string, pattern, replacement): Returns a copy of the given string where the regular expression pattern is replaced by the replacement string.
          • REGEXP_MATCH(string, pattern): Returns true if a substring matches the regex pattern.
          • REGEXP_EXTRACT(string, pattern): Returns the portion of the string matching the regular expression pattern.
          • REGEXP_EXTRACT_NTH(string, pattern, index): Returns the portion of the string that matches the regular expression pattern. The substring is matched to the nth capturing group, where n is the given index.

          Check this link this will help you http://www.tableau.com/about/blog/2015/6/become-regular-regular-expressions-39802

          1 of 1 people found this helpful
          • 2. Re: Nested IF help
            Ashish Chaudhari

            Hi Paolo,

             

            You will have to use the Regular expression to clean this up. Please follow the links that Tharashasank gave.

             

            Thanks and Regards,

            Ashish Chaudhari

            • 3. Re: Nested IF help
              Mark Fraser

              Hi Paolo

               

              REGEX functions may be helpful...

              From a quick look i can see multiple issues in your formula

               

              an IF statement uses this basic format

              IF TEST THEN TRUE ELSE FALSE END

              We can extend that with ELSEIF

              IF TEST THEN TRUE ELSEIF TEST2 THEN TRUE ELSE FALSE END

               

              Looking at the start... we have IF, test then another IF?! this should have been the true branch...

               

              IF LEFT([Title],4) = "ABC:" THEN

                 IF LEN((REPLACE(TRIM(MID([Title],1,FIND([Title],"-",1))),"ABC:","")))=0 THEN

                 "***Cannot evaluate TITLE due to missing single dash '-'"

                 ELSE

               

               

              A basic start is something like this

               

              IF LEFT([Title],4) = "ABC:" THEN RIGHT([Title],LEN([Title])-4) ELSE NULL END

              Where I have the arrow, this is where we would add additional tests...

               

              Happy to help further, if you can provide more example

               

              Cheers

              Mark

              • 4. Re: Nested IF help
                Mark Fraser

                This is the best resource for string manipulation in Tableau (non REGEX)

                String Calculations in Tableau - Clearly and Simply

                1 of 1 people found this helpful
                • 5. Re: Nested IF help
                  paolo.raia

                  Thank you all for your replies and guidance on this.  I have used REGEX before in Excel VBA, but didn't think it was available in Tableau.  I have version 9.2 but it doesn't appear as a function.  Is this an un-documented function?

                   

                  Kind regards.

                  • 6. Re: Nested IF help
                    paolo.raia

                    Hi Mark,

                     

                    Many thanks for your reply.  I understand the IF statement and logic, i.e.

                    IF TEST THEN TRUE ELSE FALSE END

                    ...or...

                    IF TEST THEN TRUE ELSEIF TEST2 THEN TRUE ELSE FALSE END

                     

                    But if there is an additional condition how would that work? For example:-

                    IF TEST1 = TRUE THEN

                         IF TEST2 = TRUE THEN

                         RESULT2

                         ELSE

                         NO RESULT2

                         END

                    ELSE

                    NO RESULT1

                    END

                     

                    Many thanks.

                    • 7. Re: Nested IF help
                      Mark Fraser

                      Should be from 9.0?!

                      Become a regular with regular expressions

                       

                      With regard to your question - you would normally extend with conditions by using AND or OR

                      AND for when you need multiple tests to be TRUE

                      OR when either can be true

                       

                      IF TEST1 = TRUE AND TEST2 = TRUE THEN RESULT2 ELSE NO RESULT 2 END (both must be true)

                      or

                      IF TEST1 = TRUE OR TEST2 = TRUE THEN RESULT2 ELSE NO RESULT 2 END     (either can be true)

                       

                      Logic statements can be difficult... i would advise starting small, and add each test, condition 1 at a time

                      Happy to try and help further, if I can

                      • 8. Re: Nested IF help
                        Mark Fraser

                        IF TEST1 = TRUE AND TEST2 = TRUE THEN RESULT2 ELSE NO RESULT 2 END (both must be true)

                        or

                        IF TEST1 = TRUE OR TEST2 = TRUE THEN RESULT2 ELSE NO RESULT 2 END     (either can be true)

                        Apologies, 2nd output should be NO RESULT 1

                        Hope you get the idea

                        • 9. Re: Nested IF help
                          paolo.raia

                          Yep that's cool Mark. Thanks very much for your help on this.