5 Replies Latest reply on Jan 9, 2019 10:23 AM by Paul Wachtler

    Convert Integer to Text

    zain.lone

      Hi,

       

      I have several Measures within my view. Currently, if I pull all measure values into the Marks TEXT field, the values returned are either a 1 or 0 . I'd like to replace every instance of the number 1 with the text "YES" and every instance of the number 0 with text "NO".

       

      I believe something like: ```IF [Measure Name] = 1 THEN "YES" ELSE "NO" END``` would work, but my understanding is that this would only replace one single measure's values, and I would like to replace all instances of the number(s) 1 and 0.

       

      Thank you

        • 1. Re: Convert Integer to Text
          Diego Parker

          Hey Zain, could you upload a workbook to get a better understanding of your problem?

           

          You can't apply an IF statement to the Measure Name pill (in fact you can't create a calculated field with it), therefore you would have to create a statement that addresses each measure. If i'm able to have a look at your workbook or to a mock up table that resembles it, I'll be able to provide you a much comprehensive answer 

           

           

           

          Best,

          Diego.

          • 2. Re: Convert Integer to Text
            Martin Peak

            HI Zain,

             

            Diego is right - you can't change all of your measures at the same time if you have your datasource set up with 1 column for each measure. Sometimes it is better to restructure your datasource to enable certain things in tableau - for example if your dataset was survey questions and responses you could change it from

                                                    

            ResponseIDQ1Q2Q3
            1101
            2010

             

            to

                                                                                    

            ResponseIDQuestionResponse
            1Q11
            1Q20
            1Q31
            2Q10
            2Q21
            2Q30

             

            The first structure is good for regression and modelling and certain types of calculations, whereas the second structure is quite strong for displaying and comparing questions against each other, and I've found it to be the best for survey question and response sets. It has the added benefit that you can change the 1's to Yes for every question at the same time.

             

            There are many ways to make this change, tableau has this built in Pivot Data from Columns to Rows - Tableau, or you can use external tools such as Tableau prep, R or do it manually if your dataset is small.

             

            You might also want to check out the alias functionality in tableau, it means you can change the 1's to Yes without having to create another field. - Create Aliases to Rename Members in the View - Tableau

             

            Cheers,

            Martin

            • 3. Re: Convert Integer to Text
              Paul Wachtler

              Hi Zain,

               

              There's an easy way to accomplish this with custom number formatting.

               

              Right click on "Measure Values" on your text mark and select Format.  On the Pane tab in the formatting menu, under Default, click into "Numbers", then select Custom, and in the box that pops up enter this (including the commas):

              "YES";;"NO"

               

              This will change any positive numbers to "YES" (in this case, that will be all your 1 values).  The second value between the two semi-colons is blank since you don't have any negative values.  Then the last value sets any 0 values to "NO".

               

              Here's how it looks in an example I mocked up.  The "YES" values were all 1s, and the "NO" values were zeros.

              Screen Shot 2019-01-08 at 9.12.39 PM.png

               

              You can check this out in the attached workbook too.  Let me know if you have any questions.

               

              Best,

              Paul

              1 of 1 people found this helpful
              • 4. Re: Convert Integer to Text
                zain.lone

                Thank you! This is exactly what I needed

                • 5. Re: Convert Integer to Text
                  Paul Wachtler

                  Welcome! Happy to help!