Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Bug: Reading numbers from Excel can produce floats

seven
12 - Quasar

When Alteryx 2018.4.3.54046 reads an xlsx, it can "introduce" a float where there was none before. This only happens in a specific situation and it's not clear WHY it is doing this. The actual numbers are exact to 2 and 3 decimal places in Excel. It isn't a matter of formatting or display. That's what they are. An example number: 1.43.

 

If there is a string value in the same column, then Alteryx will read the entire column as a string. So far, so good. When it does this, some of the numbers now look like floats. 1.43 now looks like 1.42999999998 in Alteryx. Not all of the numbers get this weird float treatment.

 

I cannot control the source files and they may have strings in various cells of the "numeric" fields. I have to read everything as strings. I want to know why this happens and what to do about it. Thank you.

7 REPLIES 7
ThizViz
11 - Bolide

I had this situation early on and it drove me nuts. Nothing I did could change it, until I had a video chat with support.

 

In my case, there were NULLs in my number fields of which I was not aware. Once I added a NULL to zero formula, I had no more problems. Not sure if it will help you or not. 

 

if isnull([field]) then 0 else [field] endif

@thizviz aka cbridges, Bolide
http://community.alteryx.com/t5/user/viewprofilepage/user-id/2328
TonyaS
Alteryx
Alteryx

@KylieF  can we move this one to B&I and have someone try to reproduce? One of our Devs is not able to reproduce this one so it might be fixed or there might be something very specific about the input file that we need to know to reproduce it. 

Tonya Smith
Sr. Technical Product Manager, cloud App Builder
KylieF
Alteryx Community Team
Alteryx Community Team

@TonyaS 

I've moved this post to Designer Discussions, since that's Community's best location for bugs and have escalated this post to get Support to attempt to reproduce.

________________________________________________________
Program Manager - Community Platform, Alteryx
ntobon
Alteryx
Alteryx

@seven   Could you please provide sample data if possible, so we can easily test on our end?

ntobon
Alteryx
Alteryx

@kksieski   We haven't been able to reproduce the issue. I have not heard from you.  Could you please provide sample data (excel file) , so we can easily test on our end? 

aklaum
5 - Atom

I am currently having the same issue.  Attached are the files I used to reproduce it.  The first float is introduced in record 171 column F53.  Let me know if any other information is needed.

seven
12 - Quasar

I found a solution:

change the .xlsx to .zip, unzip the file, and examine the XML docs inside.

 

This post helped me to understand the process.

ttps://professor-excel.com/xml-zip-excel-file-structure/

 

Turns out Microsoft Excel lies to us sometimes when it presents the data. Alteryx sees through to what actually is in the doc, not what is presented to us.

Labels