Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I am importing data from Navision and I am not happy with the way that the numeric data looks:
All the numeric values are monetary values and should have 2 decimal places (i.e. 0,15 and not 0,14999999999999900) as per example below
I am not sure what I need to do to achieve this during loadup. Can you assist?
Yes I understand that your numbers are money. I'm sorry I said "fraction" as part of my explanation as you seem to have locked onto that and decided I'm not understanding or explaining your problem. I understand your problem. I am explaining your problem. I am telling you that it is impossible to store 0.15 as a binary number, and explaining why, and explaining how you can solve the problem if it matters enough to you. I am not trying to tell you that .149999999 is a feature. I am telling you that it is the inevitable result of choosing base 2 to represent base 10 decimals.
You can argue that it is a glitch or error. I would actually agree with you, even if hic disagrees in his blog post. But I believe I understand why it not only WON'T be fixed, but SHOULDN'T be fixed. To fix it, QlikView would have to use a decimal floating point. For the purposes of QlikView, speed is probably more important to most people in most situations than the accuracy you're wanting, and I suspect that most hardware processes binary floating point much faster than decimal floating point.
So we live with it. You're probably one of the few people who even noticed it - congratulations! 
But QlikView is a BI system, not an accounting system. We're usually trying to understand the big picture, not balance to ten decimal places and satisfy our auditors and the government. If you NEED your two decimal place numbers to be EXACT, then store your numbers in cents instead of Euros (multiply by 100 before storing the data in QlikView), and you'll have an exact representation. It's a mild pain, but that's what you'd need to do. But I'm willing to bet you don't actually need to do it. Probably all you need to do is stop displaying the number that is actually being stored, and just format it with two decimal places like your users will want to see. You'll then see the number you expect to see.
Actually, I'm unclear if the problem is in Navision or QlikView in your specific case, but the explanation is the same. It's just worth mentioning because if the problem already exists in the exported data, multiplying by 100 won't fix it. But again, it probably doesn't need to be fixed.
Please read the blog post swuehl linked to, Rounding Errors, if there's any remaining confusion.
