topic Re: problem at Decimal Comparison in QlikView

problem at Decimal Comparison

Thu, 06 Dec 2012 10:07:55 GMT

Hi All,

I came across a surprising situation during decimal values comparision,

for example I have executed the below inline statement:

LOAD * INLINE [

0.15

];

and I created the a text box with definition:

=IF(A >= 0.15, 'True', 'False')

to my surprise the result I can see in the text box is 'False'

I have tried using the Num function but not much of use, I am using QV9 SR6. Has any body faced this earlier? any known resolution?

Thanks in advance,

Raj

Re: problem at Decimal Comparison

sivarajs — Thu, 06 Dec 2012 10:19:29 GMT

Try by loading

Load

text(field) as field;

This might funny

Change your expression IF(A >= '0.15' 'True', 'False')

Re: problem at Decimal Comparison

flipside — Thu, 06 Dec 2012 11:26:38 GMT

Hmmm, this really sounds like a bug or very inconsistent behaviour.

If you load the following ...

NumTest:

LOAD * INLINE [

];

... then create this expression =if((A/100)>=0.15,'True','False') it also evaluates to False, which is clearly incorrect. As Sivaraj has noted, you can wrap quotes around the value to force it to calculate correctly. However, if you develop an application using integers, such as =if(A>=15,'True','False'), this does NOT require the quotes, so if decimals are introduced later, there will be errors introduced.

I'm using QV11 SR1, so no changes in functionality since QV9.

I would log a call with QV.

flipside

Re: problem at Decimal Comparison

Thu, 06 Dec 2012 11:40:13 GMT

Hi!!

Try...with 0.15 (dot), not 0,15 (comma)

=IF(A >= 0.15, 'True', 'False')

Rebeca

Re: problem at Decimal Comparison

flipside — Thu, 06 Dec 2012 11:53:57 GMT

The OP already used a dot, however I wonder if this is how the anomaly arose. In Sweden (according to Wikipedia) the decimal separator is a comma, and so you would HAVE to wrap the value in quotes otherwise the statement would fail due to the decimal part becoming the TRUE evaluation part of the IF. Here in the UK we use a dot and this doesn't fail the statement if you don't use quotes.

flipside

Re: problem at Decimal Comparison

richnorris — Thu, 06 Dec 2012 11:56:12 GMT

Yeah, so putting quotes around the decimal fixes it, but if the result isn't a decimal then internally it is working correctly. i.e.; If you do something like:

=IF(num(A)*100 = 15, 'Correct', 'Incorrect')

(with num(A) being 0.15)

then it works fine, so you don't need to accomodate for the decimal unless putting it in your if clause. I think that as long as you are writing a decimal anywhere you remember to quote it, it should be ok? Are there any circumstances where this would be a problem though?

Re: problem at Decimal Comparison

Or — Thu, 06 Dec 2012 12:17:59 GMT

if(A>=0.14999999999999999,'True','False') - will return False for A=0.15

if(A>=0.1499999999999999,'True','False') - will return True for A=0.15

I guess that gives us the cutoff for how QlikView floats decimals.

Curiously,

if(A>=round(0.15,0.01),'True','False') will return True for A=0.15, so I guess it does have the ability to handle correctly. You can use that if you don't want to use the string version, I suppose.

Re: problem at Decimal Comparison

hic — Thu, 06 Dec 2012 14:47:37 GMT

orsh_ is spot on.

You should never test real numbers for equality. Most decimal numbers cannot be represented correctly in a binary form, so an equality test is meaningless. There is a lot written about this, e.g. http://www.cygnus-software.com/papers/comparingfloats/comparingfloats.htm.

In this specific case, you have a comparison inside the if-function [A>=0.15]. The expression left of the relational operator goes through an aggregation step (there may exist several values of A) that the right side does not have. I suspect that this step causes the difference between the left and the right side of the comparison.

HIC

Re: problem at Decimal Comparison

Tue, 11 Dec 2012 10:19:41 GMT

Thanks Orsh_ !

that resolved the problem, Nice anology!