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

# How to compare string and integer value

Hi guys,

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

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
```

Best

Ramon

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

this is the data file

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

Hi Ramon

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

thanks

canny

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

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.

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

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

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