10 Replies Latest reply on Oct 24, 2013 6:12 PM by Ville Tyrväinen

# Filter number data based on special character i.e. <10

Hey there, I'm trying to say that any number which appears in my data as <10 should be treated as the number value 1

i.e. the following data:

number
500
600
700
<10
50
<10

in tableau should be used as:

number
500
600
700
1
50
1

how would this be done with a calculated field? i have tried a few different methods and failed miserably

• ###### 1. Re: Filter number data based on special character i.e. <10

David welcome to the forums!

IF [number]<10 THEN 1 ELSE [number] END

In your example that will convert all values that are less than 10 to 1. But once you try this and it doesn't work the way you want it to, please come back and help us understand why it didn't work, by posting a packaged workbook with a bit of sample data that represents what you are actually trying to accomplish.

Tableau is both wonderful and complex. Tableau is a chameleon that changes/morphs into many different skins, depending on what you drag-n-drop into the view. Without understanding what you have in the view, and what you want to accomplish it is very difficult to explain to you how to control this fickle chameleon we have all come to know and love.

--Shawn

• ###### 2. Re: Re: Filter number data based on special character i.e. <10

Yes the formula does not work what i'm meaning is that the data is stored as the value "<10" and I'm wanting tableau to treat all those "<10" values in the data as 1 but not meaning than the data values are less than ten.

attached is my sample data and workbook showing how this data is not recognized by tableau.

• ###### 3. Re: Filter number data based on special character i.e. <10

Shawn will give you better answer..but if your data has values for each impressions you could just write ifnull(impressions,1)

• ###### 4. Re: Filter number data based on special character i.e. <10

If you can modify your data before importing it to Tableau, change impressions data type from general to text. Then in Tableau create calculated field "if impressions="<10" then 1 else int(impressions)"... But in that case you could also change those values so maybe it's not a real solution

1 of 1 people found this helpful
• ###### 5. Re: Filter number data based on special character i.e. <10

David, I'm very glad Ville was able to answer your question -- the forums are absolutely great that way, everybody jumps in to help. If you find the answer doesn't work, please come back and let us know.

--Shawn

PS: Actually, if you find Ville's answer works, also let us know. Half our battle of helping forum folks is knowing/remembering where the answer is! [Beacause almost all questions are NOT original, and have been answered before by the true Tableau luminaries: Joe, Richard, Robert, James, Jonathan.... and Alex, of course.]

--Shawn

• ###### 6. Re: Filter number data based on special character i.e. <10

it seems that Ville's first response is working for me, but only because Tableau does not recognize the number value "<10" as entered in the data, however it recognizes it as a null value. So as long as tableau works this way I will not have a problem

• ###### 7. Re: Filter number data based on special character i.e. <10

Perfect!

--Shawn

PS: But hope you stay engaged in our community, even if you don't have an immediate need/question.

• ###### 8. Re: Filter number data based on special character i.e. <10

Shawn Wallwork is it possible to determine data format when data is coming from database? I mean if that impressions is set to be number in database and there are values like <10, Tableau won't recognize it? And if there are also empty values, you can't use that ifnull. I'm still little bit confused about that, when Tableau recognize values and you can change eg. from text to number. I tested with csv and schema.ini file:

When I set type=text,  I see :

When I set type=DateTime, I see:

And when I set type=Short(integer), I see:

So if the data format is number and there are numbers with characters, Tableau recognize them as numbers and will show "Null" values. And then you can't change the format in Tableau or even see values?

Ville

• ###### 9. Re: Filter number data based on special character i.e. <10

This is one of the issues with the JET engine tableau uses to import CSV, Text files and the like. The JET import process is determining the column as containing numeric data only and the value "<10" is dropped as its not compliant with that data type.

Once the data has been imported into tableau via this method, I dont think changing the data types directly in Tableau has any impact as its already been nulled out during import.

If you create a schema.ini file as in your example you can over-ride the settings and when Tableau connects to the file the JET connector should pick up all the data within the column as its using the Schema file rather than the default settings.

Roger - there's a KB article on this if you need a hand on what to do;

http://kb.tableausoftware.com/articles/knowledgebase/tableau-does-not-correctly-recognize-excel-columns

• ###### 10. Re: Filter number data based on special character i.e. <10

Thx Andy!

Ville