topic Re: How to retain Numeric format (e.g. 0.00 or 0.000000000) during load and calculations in QlikView

How to retain Numeric format (e.g. 0.00 or 0.000000000) during load and calculations

Thu, 19 Dec 2013 19:58:53 GMT

How can I force QlikView to retain the numeric format of data it is loading instead of changing it?

I am trying to load amount fields (2 decimal places) and do some calculations with them.

Most of the time this works, but in some instances QlikView is changing the format of the number.

For instance, instead of keeping 30.36, it is coming in as 30.3599999999997.

This is throwing off all the calculations - and when I try to do a check to see if two numbers match they don't.

Strangely, even if I round the numbers first, they are not matching.

I am sure I am missing something simple.

Help!

Re: How to retain Numeric format (e.g. 0.00 or 0.000000000) during load and calculations

Thu, 19 Dec 2013 20:11:59 GMT

Please try with Num function like below:

Num(11.296 , '###0.00') --> Num function automatically rounds the value into decimals as you mentioned.

Load Num(Amout,'###0.00') AS Amout,

Num((Amount * Units) ,'###0.00') AS NetSaleAmount

From Source;

Re: How to retain Numeric format (e.g. 0.00 or 0.000000000) during load and calculations

Thu, 19 Dec 2013 21:03:51 GMT

Okay I did get Round to work, but had to put it on each numeric field in the calculation - as well as outside the entire calculation.

There must be an easier way!

Re: How to retain Numeric format (e.g. 0.00 or 0.000000000) during load and calculations

Thu, 19 Dec 2013 21:09:34 GMT

Just outside of entire calculation.

Re: How to retain Numeric format (e.g. 0.00 or 0.000000000) during load and calculations

Thu, 19 Dec 2013 21:26:53 GMT

Hello,

try something like this:

MyTable:

Load *,

Amount * Rate as AmountOK;

Load

Num(Amount,'#,##0.00') as Amount,

Num(Rate, '#,##0.00') as Rate

From Table1;

or to be sure

MyTable:

Load *,

Num(Amount * Rate,'#,##0.00') as AmountOK;

Load

Num(Amount,'#,##0.00') as Amount,

Num(Rate, '#,##0.00') as Rate

From Table1;

but I think the first option works.

Best Regards.

Re: How to retain Numeric format (e.g. 0.00 or 0.000000000) during load and calculations

Josh_Good — Thu, 19 Dec 2013 22:06:43 GMT

You can set the numeric formatting for any field by going to Setting -> Document Properties and selecting the Number tab. This will set the formatting for the entire document (except when it is overridden at the object or expression level)