Trobles Converting fields (Oracle) for to Qlikview

[MarcoARaymundo]

Hi!

In the Oracle database, i have a fields type Number(), when i converted this field to qlikview field, the conversion resulted some problems.

Ex: Num(Field Oracle) as Field Qlikview

Oracle field	Qlikview field result
521,00	521,00000000002153
342,13	342,13000000000000
95,62	95,61999999999999

OBS: coma is a decimal point

I try: Money(Field Oracle), Num(Left(Text(Num(Field Oracle)), Index(Text(Num(Field Oracle)), ',') + 2)... and others, but the problem persist.

Oracle version: 11g Release 11.2.0.4.0 - 64bit Production PL/SQL Release 11.2.0.4.0 - Production "CORE 11.2.0.4.0 Production"

TNS for Linux: Version 11.2.0.4.0 - Production NLSRTL Version 11.2.0.4.0 - Production

Qlikview version: 11.20.11922.0 SR2 64-bit Edition (x64)

Can anyone help me?

[Gysbert_Wassenaar]

try round([Field Oracle],0.01)

[MarcoARaymundo]

Hi Gysbert,

I tried this and: Floor([Field Oracle], 0.01).

But, the problem persist.

More tips? please.

[Gysbert_Wassenaar]

Just to be sure, though it shouldn't matter at all.... do you have the decimal separator variable defined in your script?

SET DecimalSep=',';

Do you use the round/floor in the sql statement itself or the preceding load statement?

[Colin-Albert]

Have a look at this blog by Henric Cronström which explains the rounding you are seeing.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/12/17/rounding-errors

[MarcoARaymundo]

Gysbert

My variables:

SET ThousandSep='.';

SET DecimalSep=',';

No, No, I extract data from SQL in a qvw, and another qvw make the conversions.

[maxgro]

with this data in oracle (SAL column,  comma is decimal separator)

In Qlikview I get this

SET ThousandSep='.';

SET DecimalSep=',';

Table:

NoConcatenate

load

*;

SQL SELECT * FROM TEST.EMP where sal < 1000

;

or this

SET ThousandSep=',';

SET DecimalSep='.';

Table:

NoConcatenate

load

*;

SQL SELECT * FROM TEST.EMP where sal < 1000

;

[MarcoARaymundo]

Thanks for share Colin.

I read the post, but there is no indication of how to handle this situation.

My problem is to convert the number zero, some times the return is something like:

0.00000000001000

[MarcoARaymundo]

Massimo, try format number like 0,0000000000000 and verify the results.

[maxgro]

added a 0

qlikview 11.2 SR5 64

oracle 11

oracle ddl

CREATE TABLE TEST.EMP

(

  EMPNO     NUMBER(4)                           NOT NULL,

  ENAME     VARCHAR2(10 BYTE)                       NULL,

  JOB       VARCHAR2(9 BYTE)                        NULL,

  MGR       NUMBER(4)                               NULL,

  HIREDATE  DATE                                    NULL,

  SAL       NUMBER(7,2)                             NULL,

  COMM      NUMBER(7,2)                             NULL,

  DEPTNO    NUMBER(2)                               NULL

)