Double Triggers Error with Tableau Output Tool if field has whole numbers and decimals

[hellyars]

Another day, another hiccup...

Can't seem to get an hourly rate field to output as a number (a double) when trying to use the Tableau Output tool.

Error: Tableau Output (32): Got an invalid value '33.5' for column "RATE", it must be a float instance  OR

Error: Tableau Output (32): Got an invalid value '17.51' for column "RATE", it must be a float instance

The field [RATE] is converted from a String to a Double using a Multi-Field Tool and the toNumber() expression.

This keeps 22.15 as 22.15, but it converts 28.00 to 28 and 33.50 to 33.5.

I use a Data Cleansing tool in combination with an Imputation Tool to remove all extra white-space and and convert any null() values to zero.

I can get the Tableau Output tool to complete if I skip the toNumber() approach and use the Select Tool to force RATE to a Double.

BUT, Tableau interprets RATE as a Text field -- AND you can't change the data type of a field in a published dataset.


4/4 ADD:

See below.  Problem with Alteryx Tableau Output tool is not limited to this scenario (trying to export a number as a number).  

I previously had the same unresolved problem with an Alteryx date field configured as yyyy-mm-dd being interpreted as a string by Tableau from the published datasource.

The Alteryx Tableau Output Tool is the common denominator.

[jdminton]

Have you tried a select tool just before your output to see what is being sent to tableau?

[hellyars]

Yes. 

Note error to Tableau Output but not Output tool set to hypervisor (for demonstrative purposes, not possible for scenario).

[jdminton]

I don't have a tableau output tool to test this, but it seems that there might be something in the xml that is either providing the wrong data type or Tableau is picking up the wrong data type. The other option is that since the ToNumber converts to double, tableau may not like that. Are you running into issues using the select tool to convert or are you just wanting it dynamic?

Try ToNumber([Rate],1,0,".") in your multifield formula tool and see if you get different results. You might also want to look at the data that is failing (is it every record?) and see if some records are not getting the error. If that is the case, you might need to do something else to the string value before converting to number. Maybe a ToString([Rate],2) to set the number of decimal places first before doing the ToNumber(). It's definitely an uncommon error you're running into. Please let me know how it goes.

[hellyars]

@jdminton Thank you.

The data is every record.  The error always references value in the output (no matter how the output is filtered/sorted).

toNumber to Double in Alteryx >>> Alteryx Tableau Output Tool >>> NO JOY >>> Error

String in Alteryx >>> Alteryx Tableau Output Tool >>> NO JOY >>> Results in Tableau String and you can't change the the field type in a published data source

Select to Double >>> Tableau Output Tool >>>> NO JOY>>> Results in Tableau interpreting field as a String AND you can't change field type in a published data source

Select to Double or String >>> Output Tool (set to output .hyper) >>>PARTIAL SUCCESS>>> RATE is a number or the data type can be changed to a number in Tableau because it is not a published data source >>> BUT this requires to save locally and then use Tableau to upload to Tableau Server >>> defeats the purpose>>> LESS JOY

ToNumber([Rate],1,0,".") = NO JOY = same error

To your point, the error seems to be related to the Tableau Output Tool and saving direct to Tableau Server/Cloud (which is the purpose of the Tableau Output Tool).

My running hypothesis is that it is related to how the Tableau Output Tool interprets the missing .0 or .00?

Can you force a NUMBER as a NUMBER in Alteryx to have X decimal places (without forcing it to be a String)?

[hellyars]

@jdminton 

I just realized I had the same problem with the Alteryx Tableau Output Tool with dates.  

DATES configured as yyyy-mm-dd in Alteryx would pop up in Tableau as a STRING when published to Tableau Server/Cloud from the Alteryx Tableau Output Tool.

And, you could not change to a DATE because you can't change the data type of a published data source.

Seems related?

[hellyars · Accepted solution]

@jdminton 

I figured it out. 

SOLUTION  >>>  USE FIXED DECIMAL NOT DOUBLE

I used the Multi-Field Tool to convert RATE to a Fixed Decimal.  

This ensures that the RATE is a Number in the Tableau published data source when using the Alteryx Tableau Output Tool.

When using the Multi-Field Tool to convert a field to a Fixed Decimal the default size of the field (or new field) is set to 19.6

The default size of 19.6 will convert 17.51 to 17.510000 OR 6 decimal places.

So changing the default size to 19.2 ensures that 17.51 remains 17.51 and that 22.00 remains 22.00 and does not become 22.