3 Replies Latest reply on Jul 25, 2018 10:58 AM by Joe Geglio

    String Calculation - what am I missing?

    Joe Geglio

      A year ago I created a dashboard with a calculation that changed an all-caps string field so that only the first letter of each word in a space-delimited string was capitalized. The calculation did this for up to 8 words separated by spaces, which was the maximum number of words in any of my cases, but also worked for cases with less than the maximum 8 words. You can see this calculation working just fine in the attached '2015-2016' workbook.

       

      Working with a new set of data, I recently tried recreating this calculation in a new workbook (see the attached '2016-2015' workbook). However, in this instance the calculation is only applying to cases with the maximum number of space-delimited words allotted in the calculation (in this case, 3). For all cases with less than 3 words, Tableau is returning a Null value.

       

      I'm looking at these two workbooks and I cannot figure out why the calculation works for all cases in the first instance, but is creating Nulls in the second. Since the calculations are the same, I'm guessing it's a formatting issue that I'm just not spotting. But I've been looking at this all morning and I think I've developed tunnel vision. Hoping another set of eyes can catch the problem.

       

      Thanks!

      Joe

        • 1. Re: String Calculation - what am I missing?
          Simon Runc

          hi Joe,

           

          So I wasn't aware of this, but looks like the way a SPLIT returns, when there is no SPLIT to Return, has changed and now returns a NULL (before it must have returned an empty string)

           

          With string concatenations (like Arithmetic) 10 + NULL = NULL (Not 10) and "A" + NULL = NULL (Not "A")

           

          If you wrap the final 2 checks in NULL handling (IFNULL) that should do the trick

           

          IF LEN(TRIM([NSC College]))>0 THEN

           

           

          UPPER(LEFT(TRIM(SPLIT([NSC College],' ',1)),1)) +

          LOWER(RIGHT(TRIM(SPLIT([NSC College],' ',1)),LEN(SPLIT([NSC College],' ',1))-1))

          +' '+

          IFNULL(UPPER(LEFT(TRIM(SPLIT([NSC College],' ',2)),1)) +

          LOWER(RIGHT(TRIM(SPLIT([NSC College],' ',2)),LEN(SPLIT([NSC College],' ',2))-1)),'')

          +' '+

          IFNULL(UPPER(LEFT(TRIM(SPLIT([NSC College],' ',3)),1)) +

          LOWER(RIGHT(TRIM(SPLIT([NSC College],' ',3)),LEN(SPLIT([NSC College],' ',3))-1)),'')

          END

           

          Hope that helps

          3 of 3 people found this helpful
          • 2. Re: String Calculation - what am I missing?
            Jim Dehner

            Hi Joe

            see the attached

             

            the formula was looking for a 3rd split but on some entries there were only 2 - I changed it as shown

             

             

            it now returns this

             

             

            Jim

             

            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

            2 of 2 people found this helpful
            • 3. Re: String Calculation - what am I missing?
              Joe Geglio

              This works great, thanks!