topic Re: Trobles Converting fields (Oracle) for to Qlikview in QlikView

Trobles Converting fields (Oracle) for to Qlikview

MarcoARaymundo — Fri, 21 Mar 2014 12:28:14 GMT

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?

Re: Trobles Converting fields (Oracle) for to Qlikview

Gysbert_Wassenaar — Fri, 21 Mar 2014 12:34:30 GMT

try round([Field Oracle],0.01)

Re: Trobles Converting fields (Oracle) for to Qlikview

MarcoARaymundo — Fri, 21 Mar 2014 13:35:46 GMT

Hi Gysbert,

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

But, the problem persist.

More tips? please.

Re: Trobles Converting fields (Oracle) for to Qlikview

Gysbert_Wassenaar — Fri, 21 Mar 2014 13:55:38 GMT

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?

Re: Trobles Converting fields (Oracle) for to Qlikview

Colin-Albert — Fri, 21 Mar 2014 14:02:10 GMT

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

Re: Trobles Converting fields (Oracle) for to Qlikview

MarcoARaymundo — Fri, 21 Mar 2014 14:15:46 GMT

Gysbert

My variables:

SET ThousandSep='.';

SET DecimalSep=',';

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

Re: Trobles Converting fields (Oracle) for to Qlikview

maxgro — Fri, 21 Mar 2014 14:21:30 GMT

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

;

Re: Trobles Converting fields (Oracle) for to Qlikview

MarcoARaymundo — Fri, 21 Mar 2014 14:42:25 GMT

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

Re: Trobles Converting fields (Oracle) for to Qlikview

MarcoARaymundo — Fri, 21 Mar 2014 14:50:14 GMT

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

Re: Trobles Converting fields (Oracle) for to Qlikview

maxgro — Fri, 21 Mar 2014 15:07:28 GMT

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

)

Re: Trobles Converting fields (Oracle) for to Qlikview

MarcoARaymundo — Mon, 24 Mar 2014 13:14:26 GMT

Massimo, unfortunately I can't change the fields.

Re: Trobles Converting fields (Oracle) for to Qlikview

ThornOfCrowns — Mon, 24 Mar 2014 13:37:43 GMT

Have you tried forcing the format such as MONEY(FIELDNAME,'€#.###,00')?

Re: Trobles Converting fields (Oracle) for to Qlikview

MarcoARaymundo — Mon, 24 Mar 2014 13:41:44 GMT

James I've tested it, and the result was the same.

Re: Trobles Converting fields (Oracle) for to Qlikview

ordre — Mon, 24 Mar 2014 16:14:49 GMT

trying changing the connection string ODBC/OLEDB may help you. I did not remember the exact driver but if you are using ODBC please try OLEDB or vice versa.

Re: Trobles Converting fields (Oracle) for to Qlikview

MarcoARaymundo — Mon, 24 Mar 2014 18:48:26 GMT

Emre I using ODBC connection and I change by OLEDB like your suggestion, the problem persist.

Oracle drive version: SQORA32.dll 11.02.00.03

thanks

Re: Trobles Converting fields (Oracle) for to Qlikview

MarcoARaymundo — Wed, 02 Apr 2014 18:52:14 GMT

Hi!

I tried various ways and only got hit when I changed the number format by Oracle. Like this:

TO_CHAR (FIELD, '000000000000000D00 ') AS FieldChanged

Return a string that was '000000000000000 .00 ', in my country the decimal separator is a comma.

So in QV simple character substitution converted the number correctly.

Replace (FieldChanged, '.', ',')

Thanks.