topic Re: limited rounding in QlikView

limited rounding

Anonymous — Fri, 05 Jun 2015 11:09:43 GMT

Hi everybody!

I need to round numbers by the 19th decimal place.

In the menu of QlikView there is a possibility to round by the 14th place.

To round within the script or in a table didn't work with these numbers.

I think it will work with an if-loop, but that will only work exactly by a

repeat of a querry for each decimal place.

Somebody with an equal problem or a simpler answer?

Have a nice weekend!

Re: limited rounding

Anonymous — Fri, 05 Jun 2015 11:13:19 GMT

A guess but how about multiplying your number by 1,000,000,000,000,000,000,000,000, rounding it then dividing it by the same number. I have not counted number of zeroes I typed & expect it to be wrong.

Re: limited rounding

swuehl — Fri, 05 Jun 2015 11:19:23 GMT

I believe 19 significant decimal digits is not supported by the floating point format used by QV.

Probably someone from QT could tell you more, e.g.hic

Re: limited rounding

Anonymous — Fri, 05 Jun 2015 11:22:40 GMT

I tried, but this didn't work.

Re: limited rounding

hic — Fri, 05 Jun 2015 11:27:01 GMT

Stefan is right. QlikView uses a 64-bit binary internally (See Double precision) and this means 14, sometimes 15, significant digits. Not more.

HIC

Re: limited rounding

marcus_sommer — Fri, 05 Jun 2015 11:28:27 GMT

Rounding especially by so many decimals will be difficult: Rounding Errors and I'm not sure how many numbers and decimals qv could handle within the script - in gui it's max. 14 decimals. But you could try to display them as text. Maybe you could multiply your number with some big number, then rounding and afterwards you divide it again, something like:

round(Number * pow(10, 12)) / pow(10, 12)

If you handle it completely as text and checked the 19th decimal and replace it - it will work but I think these approach will have some other disadvantages.

- Marcus

Re: limited rounding

Anonymous — Fri, 05 Jun 2015 11:36:58 GMT

I tried to separate a first part as text and rounded the last one.

If the last part is rounded from 9 to 10 it should effect the first

part. And that ist the problem. It causes some loops to prevent

a chain when there are some '9'. Maybe for each digit.

Re: limited rounding

marcus_sommer — Fri, 05 Jun 2015 11:54:29 GMT

I'm not sure if this would work at once, maybe it needs some adjustments with num() or on the syntax:

text(floor(Number) & left(subfield(Number, ',' , 2), 17) &

mid(subfield(Number, ',' , 2), 18, 1) + round(mid(subfield(Number, ',' , 2), 19, 1) / 10))

But in general I have doubts if it will be a good idea to handle such numbers within complex string-operations and not in a specialized tool maybe mathematica and using whose output in qv.

- Marcus

Re: limited rounding

Clever_Anjos — Fri, 05 Jun 2015 12:15:54 GMT

It could be easier if you multiply by a factor (1.000.000 for example) before loading into QlikView,

Same way

1 mg -> 1 gram/1.000.000

Re: limited rounding

Anonymous — Wed, 10 Jun 2015 08:03:18 GMT

The database is from sql. Is it possible to cache transformed data before loading into QlikView without using another tool?

Re: limited rounding

Clever_Anjos — Wed, 10 Jun 2015 11:44:59 GMT

Yes,

Are you using a SQL SELECT or a procedure that has a SQL SELECT within ritgh?

So try something like

SELECT

myfield/1000000 as myFieldTransformed

[other fields]

FROM mytable;

Re: limited rounding

petter — Wed, 10 Jun 2015 17:36:57 GMT

Which system does the 19 decimal numbers come from? What data type is it? If there is indeed a super high-precision floating point or decimal type of number it should be quite easy to round it by trying to get it in as a text string and then just pulling out the last few digits and based on a simple if round it to 14 digits floating point precision.

Going via any number format in QlikView and dividing or multiplying from that will never work as long as you try to treat the number as a single entity.

If you give us information on where the numbers come from. A SQL database? A CSV-file / Text-file or something? We could be able to give you better suggestions or solutions....