Just using the FLOAT() function will return the number for the ones that can convert and NULL for the ones that can't. Or if you prefer zero you can use IFNULL(FLOAT([x]),0).
Or is that not working for you? I can imagine there might be slightly different behaviour with some datasources, but I wouldn't have thought so.
When I attempt that, I get the following error:
Database error 0x80040E07: Error converting data type varchar to float.
This is the formula I used: ifnull(float([Quote Amount]),0), where Quote Amount is a string.
I just Googled your error message and the second hit was this Tableau Knowledgebase article about SQL Server - is that your data source?
If I understood that article correctly, SQL Server will return NULL if you try FLOAT("Rubbish") but that error if you try FLOAT("123,456"). Unfortunately the article doesn't provide any way to get around it with cleansing your data.
Yep...you are correct. SQL Server is the data source. We'll be migrating it to Oracle soon, but as of now that's what I'm stuck with. So it sounds like the only way to deal with this is to clean up the source data. Bummer.
How many rows?
You could possibly force the conversion to happen client-side in Tableau with this expression:
Anything else you want to do with it would then have to become table calculations, so this may not work for you.
I tried the LOOKUP() trick and it does work, but I was intrigued by what was going on here so I had a bit more of a look, too.
The problem is that when Tableau tries to convert a string to a float it first tries to check whether the string is a valid number by calling the SQLSERVER ISNUMERIC() function and then if that returns 1 (true) it tries to cast the string to a float.
But there's a gotcha with using ISNUMERIC() like that, as explained in this StackExchange thread (the final posting describes it very well).
Essentially ISNUMERIC() returns true if the string can be converted to any numeric type at all, but that doesn't necessarily mean it can be converted to a float.
The most frequent causes of the issue seem to be dollar signs, commas and +/- signs, all of which are accepted for conversion to the money type, but none of which is allowed for a float (I can see that a dollar sign should be disallowed, but +/- and comma seem reasonable - anyway, that's how it is).
So here's another workaround you could use which may work better for you than the LOOKUP trick.
Try defining the following calculated field:
RAWSQL_REAL("CASE WHEN ISNUMERIC(%1) = 1 THEN CAST(CAST(%1 AS MONEY) AS FLOAT) END", [Quote Amount])
If the string is able to be converted to a number at all this first casts it as a money type (which handles the +/-, comma and even a dollar or pound sign) and then casts that as a floating point number.
I'm guessing that your quote amount fields might really be representing money anyway.
Hope you had a good Memorial Day.
Thanks for looking into this. Yes, the values in the field that are actually useful are currency values, and so being able to sum them (and ignore the string garbage in there) is the aim. Your calculation
RAWSQL_REAL("CASE WHEN ISNUMERIC(%1) = 1 THEN CAST(CAST(%1 AS MONEY) AS FLOAT) END", [Quote Amount]) worked perfectly so long as I had a live connection to the SQL Server source, but once I tried to take an extract I got the error that this is an unknown function. Any way around that? :-)
Are you still on Tableau 6 by any chance (I vaguely recall you saying you were on another thread recently).
Tableau 7 materialises the results of RAWSQL calculations on creating the extract, so this shouldn't be a problem under version 7 (as long as it's not a RAWSQLAGG_* function).
If you are on version 7 and it's not working then maybe try telling it that the calculation is a dimension (I vaguely recall that extracts only materialised dimensions at one time, though I didn't think that was the case now - but I may be confused, it';s a while since I've needed to look at this).
Let me know if you've tried it on version 7 - if that's still not working I might experiment - or we could ping Robert Morton onto the thread. Robert will know exactly what's happening - it was Robert who pointed out that RAWSQL gets materialised now in a previous thread.
If you're still on version 6 (and still can't/won't upgrade) then all I can think of is that table calc method I mentioned above.
Yes, we're still on version 6. We'll be converting over to 7 soon, but not for another month or so. When I tried using the table calc you provided (IFNULL(FLOAT(LOOKUP(ATTR([Quote Amount]),0)),0)), I still get the same error message:
Database error 0x80040E07: Error converting data type varchar to float.
Unable to create extract
Another thing I noticed is that even if I restrict the SQL query to just a timeframe when all of the values are good (i.e. all numeric), I still get the same error. Is this because Tableau is attempting to re-cast the data field before the timeframe filter is applied?
Hmmm - that table calc worked for me when I tried a very simple test the other day...
Will have another look, next time I fancy a 5 minute break from work... ;-)
Sounds good. :-)
Had a quick play, will describe the intriguing results more fully later, but the key points are:
1) Table calculation approached worked exactly as I expected for me in both 6.1 and 7.
2) RAWSQL was indeed materialised so worked fine with an extract with both 6.1.4 and 7 (I think I remember that that change happened in one of the 6.1 point releases - are you back on an earlier version of 6.1?).
Simply using FLOAT([text field]) gave the error you described in some circumstances but simply seemed to suppress the rows it couldn't cope with in other circumstances. That behaviour was definitely SQL Server side - it was confusing Tableau's caching mechanism (i.e. I had to make sure I refreshed the connection in order to force Tableau to go back to SQL Server to see the impact of some changes).
Definitely some weird stuff going on here.
First off I'll cover off what I think must be happening for you in the various things you've tried, then I'll describe what happened in my test (which I now think is a really subtle and quite nasty Tableau bug).
You said that when you tried the table calculation you still got the same error. I think that must mean that you still had the FLOAT() calculated field defined in your data source and it was being executed for some reason. If you got the error when you tried putting the table calculation on to the view when directly connected to SQL Server, then I think you must also have had the FLOAT() calculation on the view. If it happened when you tried to create an extract that will be because Tableau is trying to materialise that calculation - which means it is including the FLOAT() conversion in the query used for building the extract. Just delete that calculation from the datasource, if that is what is happening.
On the question of RAWSQL not being supported with data extracts, I checked back and support for materialising the results of RAWSQL expressions was added in 6.1.4, so I'm guessing you must be locked back in time before then. I'm also guessing that you have a locked-down desktop and an IT department with rules.
Now here's what I did.
First I created a test table in SQL Server with an id column and a text column with numbers to try to convert, like this:
Then I created 3 calculated fields to try converting the numbers:
[float] = FLOAT([text_float])
[raw_sql_float] = RAWSQL_REAL("CASE WHEN ISNUMERIC(%1) = 1 THEN CAST(CAST(%1 AS MONEY) AS FLOAT) END", [text_float])
[tc_float] = FLOAT(LOOKUP(ATTR([text_float]),0))
Then I tried dragging each of those onto the view as dimensions. [float] gave the same error as you have been getting:
The other two fields were allowed, though the table calculation returned NULL for the ones with dollars or commas, so is much less useful to you than the RAWSQL version:
All that makes perfect sense. But I then tried using SUM() of each of the calculated fields. That wasn't an option for the table calculation (it's already an aggregate),
Converting the RAWSQL version to SUM() made no difference to the answer:
But when I added SUM([float]) I got a really unexpected answer:
Tableau displays the first row and then stops because SQL Server returned an error on the second row. (I tested with more valid rows at the start and it basically returns all rows before the first invalid one.) There is no indication given to the user that there was an error and it looks as if this is the full result set of a successful query. (I've included the filter shelf in the screen shot, by the way, so you can see that there is no filter).
Looking in the Tableau logs I can see this happening quite explicitly. It behaves the same under version 6.1.4 and 7.0.4 - though it's much easier to see on version 6.1 because the error is explicitly shown in log.txt, whereas in version 7, log.txt doesn't show the error in this case and you have to look in tabprotosrv.txt (the SQL calls are now handled by a separate component).
Once the error has happened, Tableau's caching mechanism is also left confused. Taking SUM([float]) off the view leaves it like this:
At this point, hitting F5 to refresh the connection displayed the 5 rows again.
I've reported this as a bug.
Looks like you had some fun inside of the Tableau bowels. :-)
On my end, I tried re-creating the three calculated fields per your specs to see if I got anything useful. Here are the three I created, and the results I experienced:
- Quote (Float): FLOAT([Quote Amount]) This one returned the error you see in the screen-shot below.
- Quote (RAWSQL): RAWSQL_REAL("CASE WHEN ISNUMERIC(%1) = 1 THEN CAST(CAST(%1 AS MONEY) AS FLOAT) END", [Quote Amount]) This one worked fine while I was connected live to the data, but returned the 'Unknown function' error once I created an extract.
- Quote (Table Calc): IFNULL(FLOAT(LOOKUP(ATTR([Quote Amount]),0)),0) This one never returned an error, but only returned asterisks as values (see screen-shot below).
So the table calc on my machine is behaving differently than the table calc on your machine. The reason may have to do with the fact that I'm still on version 6.0.8. Since internally we never upgraded to 6.1 on Server, we also couldn't upgrade our Desktop versions to 6.1 since we would then be unable to publish to Server. I suspect you'd be able to confirm that my version doesn't handle the table calculation correctly, which would mean that I will need to wait until I am able to upgrade before this problem is resolved. Unless you can come up with another great workaround. :-)