Floating point precision

Wed, 09 Nov 2011 09:06:26 GMT

My QV application is reading figures from a QVD file that is populated with figures coming from a DB2 (iSeries) database.

The drivers used in QV are the native ones from IBM (System i access drivers for V6R1)

The database fields that are read are defined on the iSeries as 19P4 which means they can contain up to 4 decimals.

However, when transferring the data towards QV using SQL SELECT combined with sum and group by, I have figures that end up with more than 4 decimals.

As it consists of accounting figures, this is just impossible as all figures should only have max 2 decimals.

Just to give you some background info so you understand my issue :

I'm loading accounting tables that reflect the details (so called general ledger lines)

I'm also loading accounting tables that reflect the balances (so called balances)

One of my QV apps checks the consistency between both and is calculating the difference by reversing the sign of one of both amounts and then performing a sum in a table where only records are withheld where the calculated difference <> 0.

Now I end up with a whole lot of 'differences' that are shown as figures like -1.1234567124e-014 (which is actual very close to 0)

What is the explanation for this and is there any way to avoid this ?

I'm not in favour of rounding figures throughout the scripting process, that sounds like a bad idea to me.

I double checked the figures in the database and they can simply not have more than 4 decimals and no divisions or multiplication is being performed through the LOAD process.

I have attached the script in a notepad file

topic Floating point precision in QlikView

Floating point precision