Decimal places issue

[#1]

Dear All,

I have encountered a strange issue relating to decimal places and need some help.

I have a qvw which selects all data from an Oracle view using OLE DB.  It then stores the results as a qvd. When I load and display the qvd in a second, 'presentation' qvw using a table box, all numeric fields are returned with 255 decimal places (format in document properties is 'Fixed - 14 decimal places' which I believe is the max dps). The weird part is that if I add a column at the start of my load from qvd statement  e.g. Load 1 as Dummy, ColA, ColB from MyQVD.qvd then the format of the numeric columns reverts to 'Mixed' and the table shows the correct number of decimal places.

Example number returned with no dummy column in load script

0.89030000000000000002 followed by about 235zeros

Example number returned with the dummy column in the load

0.8903   (correct)

I don't know if this is how it works but I'm concerned that QV is storing 255 decimal places when 4 or 5 would be sufficient. I'm also reluctant to use the Round() function on the qvd load as there are millions of rows and about a dozen numeric columns. Ideally I don't use CAST() within the SQL select for the same reason. There must be something I'm missing as QV does display the correct number of decimal places with no other changes other than the dummy column which feels like a bodge and slows things down. Any ideas?

We have a QV v10 server running on a 64 bit machine.

Many thanks,

Ben

[johnw · Accepted solution]

I believe that QlikView is using IEEE double precision binary floating point internally.  That creates some problems, but the storage of 255 decimal places is not one of them. 

I have no idea why it's displaying all those decimal places or why the dummy column fixes it.  But generally speaking, I let QlikView do whatever it wants as far as numeric storage, and I simply format the result as desired.  To do this in the document as a whole, you can use settings -> document properties -> number.  For specific charts, it would be properties -> number.

Definitely don't do a round() during the QVD load or add a dummy column.  As you've observed, this can kill performance if it takes you from an optimized QVD load to an unoptimized QVD load.

[#3]

Thank you John. That's really useful.

I've left the loads as they are and changed the format in the document properties and that has solved my immediate problem.

Regards,

Ben