3 Replies Latest reply on Sep 12, 2017 6:07 AM by Jim Wahl

    Combining LEN function with IF THEN function problem

    Robert Wolsink

      Hello all,

       

      I have a problem with a data set.

       

      One column contains for example:

       

      file1.pdf

      file2

      file3.doc

      file4.xlsx

      file5

      file6

      file7.png

       

      Did a custom split with "." to get the extension of the files in a column called Object Extension

       

      However as file2/file5/file6 do'nt have an extension the list becomes this:

       

      pdf

      file2

      doc

      xlsx

      file5

      file6

      png

       

      I would like to replace all those longer then 4 characters with "other" or even better do it in the custom split (so if it doesn't contain a "." its replaced by "other" in the resulting field)
      Tried the following but didnt work:

       

       

       

      IF (LEN([Object Extension]) < 4) THEN [Object Extension]

      ELSE

      "Other"

       

      Any suggestions?

        • 1. Re: Combining LEN function with IF THEN function problem
          Vishal Jethwa

          Robert,

           

          It's probably not the most elegant solution but it works:

           

          IF LEN(TRIM( SPLIT( [object], ".", 2 ) )) == 0 THEN "Other" Else TRIM( SPLIT( [object], ".", 2 ) ) END

          1 of 1 people found this helpful
          • 2. Re: Combining LEN function with IF THEN function problem
            Robert Wolsink

            Thank you very much Vishal!

             

            I adjusted it a little bit and i get exactly what i want !

             

             

             

            IF LEN(TRIM( SPLIT( [Object], ".", -2 ) )) == 0 THEN "Other" Else TRIM( SPLIT( [Object], ".", -1 ) ) END
            • 3. Re: Combining LEN function with IF THEN function problem
              Jim Wahl

              Tableau also supports regular expression (regex) matching, which will make this kind of thing more robust against other periods or special characters in the file name. It's not unusual to have something like file1.0.png.

               

              For example:

              IFNULL(REGEXP_EXTRACT([Filename], "\.([^\\/*?<>:.]+$)"), "other")

               

              The regex part is \.([^\\/*?<>:.]+$) which finds a

              group     ()

              starting at the end    $

              ending with .      \.   ---period is a special character so it needs to be escaped with a \

              except   ^

              if that range includes one of these characters \\ / * ? < > : .

               

               

              Below is an example with Vishal's length-based calc, which I also like, and the regex. The disadvantage of regex is that it's difficult to understand and that not all data sources support regex.

               

              The advantage is that it's powerful and should be faster on larger data sets.

               

               

               

               

              1 of 1 people found this helpful