ROUNDING DECIMALS

[#1]

Hi All,

Please help :(.

I am trying to get decimal values from database .

In the load script i try to divide two decimal columns and get a value of 2.99.

However i am getting rounded values in my report. i.e 3

I have two bucket defined as 0-3 and 3-6.

I need 2.99 under 0-3 bucket,however it falls under 3-6.

Please help.

[johnw]

It looks like you commented in the Wiki explaining mathematical errors in QlikView shortly after making this post. Did that seem to be the explanation for your problem? Were you able to apply the workaround mentioned there - to load your decimal fields in as integers instead? Do you still need help with this problem?

[#3]

Yeah John,

I took help from there .

Yes it helped me a lot , but there is a small bug. I converted each of the columns to integer by first multiplying each and then dividing to get the ratio, as you had mentioned

But i am not able to figure out how many zeroes to multiply the column with i.e whether (column)* 100 or (column)*1000 or etc.

My columns have a length of 9 and scale of 2 in database.

Now i have two buckets : first bucket ->0-3 : defined as >=0 and ❤️ , second bucket ->3-6 : defined as >=3 and <6.

My columns values are column1:205.2 and column2: 68.4 .

My ratio is col1/col2. So if i multiply (column1*10000)/(column2*10000) , the value lies in 3-6 months bucket. But if i do

(column1*100000)/(column2*100000),the value falls in 0-3 months bucket.

Please help me. I am stuck :(.

[johnw · Accepted solution]

You need to do the multiplication before the data ever hits QlikView. If you want until the expression to do it, your data will already have been corrupted. By a length of 9 and a scale of two, do you mean your numbers are of format 1234567.89? If so, then you need to multiply by 100. You're multiplying by the number of decimal positions because you must have an integer to avoid possible corruption of the data value. But like I said, you need to do it as you read it in, as you pull from the source. So if you're pulling from an SQL database, for instance, you could do something like this:

LOAD *
;
SQL SELECT
Column1
,Column1 * 100 as Column1times100
,Column2
,Column2 * 100 as Column2times100
...
;

For list boxes and the like, you display the raw Column1. But when you need mathematical precision, you use Column1times100. So when you're setting up your buckets, you'd use something like this:

class(Column1times100/Column2times100,3)