6 Replies Latest reply on Oct 30, 2013 10:59 PM by Ramon Martinez

    How to compare string and integer value

    canny yue

      Hi guys,

       

      formula problem.png

      My [household_income] is a range like "$2000-$4000

       

      and i set this formula, it say has error, how to compare string with integer?

       

      Thanks

      Canny

        • 1. Re: How to compare string and integer value
          Ramon Martinez

          HI Canny,

           

          In your workbook, try to see the values of the field Household Income. To do that, oepn a new worksheet and drag the field [Household Income] to Rows. All the values will be displayed in the view.

           

          Take an screenshot and post it to see the set of values of that field.

           

          I believe that you have to create a calculated field with a formula like this:

           

          IF [Household Income] ="$0-$1999" THEN "Poor"
          ELSEIF [Household Income] ="$2000-$4999" THEN "Median"
          ELSE "Rich"
          END
          

           

          Check also this formula in your workbook please

           

          Best

          Ramon

          • 2. Re: Re: How to compare string and integer value
            canny yue

            this is the data file

            • 3. Re: Re: How to compare string and integer value
              canny yue

              Hi Ramon

               

              i follow your formula still cannot,it show cannot compare float and string values

               

              income calculated 1.png

              thanks

               

              canny

              • 4. Re: Re: Re: How to compare string and integer value
                Ramon Martinez

                Hi Canny

                 

                Thanks for sharing the data set.

                 

                As the field Household_Income is of type Character, we need to have a clear idea of its unique values, so I created the worksheet Sheet 1 to display those values.  

                 

                I've follow two approaches to solve your requirement

                1.- I decided to create a calculated field based on Household_Income to determine the if families are Poor, Median or Rich based on the criteria you put in the formula in your very first post. I create a calculated field called "Classification of Families" using the following formula:

                 

                IF CONTAINS([Household _Income],"below $2000") THEN "Poor" 
                ELSEIF CONTAINS([Household _Income],"$2000 to $4000") OR CONTAINS([Household _Income],"$4000 to $5000")  THEN "Median" 
                ELSE "Rich" 
                END
                

                 

                See the Sheet 2 of the workbook, where the number of families are counted and presented in a bar chart based on this new calculated field

                 

                2.- Other approach is to create a Group based on the field Household_Income, putting together those values of Household_Income according to the criteria of poor, median and rich. The Sheet 3 in the workbook shows a bar chart with the number of families by Household_Income (group).

                 

                Note that both approaches produce the same results. Compare Sheet 2 and Sheet 3

                 

                I hope this helps

                 

                Best,

                Ramon

                • 5. Re: How to compare string and integer value
                  Andy Harris

                  Canny - Just as a side note, your example data file contained some trailing spaces at the income ranges $4000 to $5000 and $5000 to $6000 - Ramon's solution will however process these correctly as the rest of the banding matches his formula.

                   

                  You will see this trailing space causing a duplication of rows if you look at Sheet1 of Ramon's workbook as there are 2 different entries for each of these bands.

                   

                  This might just be an issue with the dataset you submitted as an example but if you see this when analysing your full dataset  you might want to fix up the underlying data in Excel or simply use the Group feature in tableau  to merge the duplicate bands together in Tableau.

                  • 6. Re: How to compare string and integer value
                    Ramon Martinez

                    Hi Andy,

                     

                    Thanks you for commenting about the trailing "special" characters at the income ranges $4000 to $5000 and $5000 to $6000 which are visible in Sheet 1. Your recommendation is very valid. The data need to be cleansed before going to the analysis process.

                     

                    Best

                    Ramon