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

# String Calculation - what am I missing?

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?

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?

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?

This works great, thanks!