6 Replies Latest reply on Jun 11, 2013 9:01 PM by Shawn Wallwork

    How to change Value name?

    philip ken

      Hi,

      My data base table has the following values (all are in CAPITOL letters)

      UNITED STATES AMERICA

      CANADA

      CHINA

       

      now I want these values in below format:

      United States America

      Canada

      China

      Basically I would like to first letter in Capitol and rest are in small. Besides CASE WHEN, is there any alternative way to do this in tableau?

        • 1. Re: How to change Value name?
          Dimitri.B

          If your database has a built-in function for this, you can use Tableau's pass-through functions to get your database to do it. For example, if you are connected to Oracle and the field is called COUNTRY_NAME, then you can use the following calculated field:

           

          RAWSQL_STR("INITCAP(%1)",[COUNTRY_NAME])

           

          where INITCAP() is Oracle built-in function to do exactly what you want.

          If you want to do it entirely inside Tableau, then it is quite simple for single-word values, and becomes ridiculously complicated for multiple words like United States of America.

          • 2. Re: How to change Value name?
            philip ken

            Thank you Dimitri for your quick response. Actually I would like to perform this calculation in Tableau using Calculated field but I do not see any related function.

            • 3. Re: How to change Value name?
              Dimitri.B

              That's because there is no Tableau function that does just that - capitalises initial letter. You'll have to string a bunch of standard text functions together, and it is not going to be easy for second, third, etc. words in your text values.

              I recommend Homer Simpson approach - if you can't farm it out to the database, just give up. In my opinion it is not worth the effort.

              • 4. Re: How to change Value name?
                Shawn Wallwork

                I got most of the way there, but I agree with Dimitri, the juice isn't worth the squeeze (as Joe put it earlier today). Here's as far as I got:

                 

                IF FIND([Country],' ')>0 THEN 

                LEFT([Country],1) + LOWER(MID([Country],2,FIND([Country],' ')-1)) +

                MID([Country],FIND([Country],' '),2)

                + LOWER(MID([Country],FIND([Country],' ')+2,LEN([Country])-1-FIND([Country],' ',FIND([Country], ' ')+1)))

                + MID([Country],FIND([Country],' ',FIND([Country], ' ')+1))

                ELSEIF FIND([Country],' ')>0 THEN 'Contortions' ELSE

                LEFT([Country],1) + LOWER(RIGHT([Country],LEN([Country])-1))

                END

                 

                Which will produce this: United States AMERICA.


                If you can follow the ugly logic you can probably complete it. It will also run ugly slow on a large DB. Not recommended.

                 

                And why can't you change it in the data source?

                 

                --Shawn

                • 5. Re: Re: How to change Value name?
                  Dimitri.B

                  Since Shawn seems to be so easily bored, I'll poke this beehive a bit more.

                  Shawn, your version will not work well for GREAT BRITAIN, it will produce 'Great  BritainGREAT BRITAIN' (I hope that's not because you are an Anglophobe).

                   

                  Here is my version that works for up to two words (please be seated before looking at it):

                   

                  IF CONTAINS([COUNTRY_NAME], ' ') THEN
                      UPPER(LEFT(LEFT([COUNTRY_NAME],FIND([COUNTRY_NAME],' ')-1),1)) +
                          LOWER(RIGHT(LEFT([COUNTRY_NAME],FIND([COUNTRY_NAME],' ')-1),LEN(LEFT([COUNTRY_NAME],FIND([COUNTRY_NAME],' ')-1))-1)) + ' ' +
                      UPPER(LEFT(RIGHT([COUNTRY_NAME],LEN([COUNTRY_NAME])-FIND([COUNTRY_NAME],' ')),1))
                              + LOWER(RIGHT(RIGHT([COUNTRY_NAME],LEN([COUNTRY_NAME])-FIND([COUNTRY_NAME],' ')),LEN(RIGHT([COUNTRY_NAME],LEN([COUNTRY_NAME])-FIND([COUNTRY_NAME],' ')))-1))
                  ELSE
                      UPPER(LEFT(RIGHT([COUNTRY_NAME],LEN([COUNTRY_NAME])-FIND([COUNTRY_NAME],' ')),1))
                              + LOWER(RIGHT(RIGHT([COUNTRY_NAME],LEN([COUNTRY_NAME])-FIND([COUNTRY_NAME],' ')),LEN(RIGHT([COUNTRY_NAME],LEN([COUNTRY_NAME])-FIND([COUNTRY_NAME],' ')))-1))
                  END
                  

                   

                  At this stage I would either give up or appeal to UN to stop recognising countries with more than two words in their names.

                  • 6. Re: Re: How to change Value name?
                    Shawn Wallwork

                    I was literally only going to have it work for 3 examples he gave, so yeah you're right Great Britain wouldn't have fared well; nor Costa Rica for that matter. I took off for dinner and when I came back and saw your comments, I thought why am I banging my head against this wall when he's almost certainly not going to use it. But thanks for finishing it.

                     

                    FYI: There are not one, but two Ideas for including a PROPER() or PROPERCASE() function in Tableau. [Which me to think, somebody somewhere needs to consolidate some of these Ideas.]

                     

                    Vote away.

                     

                     

                    --Shawn