topic Number too large to parse as number? in QlikView

Number too large to parse as number?

Fri, 28 Oct 2011 09:20:37 GMT

One of my csv source files contains the large number 125195971554244 in one of the columns. QlikView Desktop (v10 SR1) refuses to interpret this as a number and will only load it as a string (with no numeric value) which obviously throws our calculations out. I suspect this is because it can't represent the number precisely in whatever internal type it is using (double precision floats?).

It is impractical to pre-/re-format the source files due to size and number. Can anyone think of a way to force QlikView to interpret this string of digits as a number? It needs to be practical and reliable so string manipulation techniques to split the value into parts and reassemble it will have to cope with decimal points and negative numbers.

The problem can be seen clearly if you try to load just that number using an inline table:

LOAD * INLINE [

125195971554244

];

The resulting single value in the F field is left-aligned by default indicating that it is just text and not being interpreted as a number.

Any advice would be greatly appreciated.

Thanks

Simon

Number too large to parse as number?

erichshiino — Fri, 28 Oct 2011 10:11:17 GMT

Hi,

I tried and it does not read as a floating point directly.

I segmented the string in two parts to force the interpretation as a number than I multiplied the part on the left to reconstruct the number. Then, it becomes a floating point.

I don't know why but with the load inline it only works if there is no tab or spaces on the left side of the line.

Hope this helps,

Erich

#####################################3

LOAD * INLINE [

125195971554244

12345

];

load F, right (F,10) AS NUMBER1,

ALT( NUM#(left(TRIM(F),LEN(TRIM(F))-10)),0) AS NUMBER2,

num#(right (F,10)) + ALT( NUM#(left(TRIM(F),LEN(TRIM(F))-10)),0)*pow(10,10) as NUMBER3

Resident t;

DROP TABLE t;

Number too large to parse as number?

Thu, 17 Nov 2011 14:40:55 GMT

Thanks Erich.

Regards

Simon

Number too large to parse as number?

johnw — Thu, 17 Nov 2011 22:36:17 GMT

And just to confirm, yes, unless something has changed in more recent versions or I'm remembering incorrectly, QlikView is using IEEE 754 double-precision binary floating point as its internal numeric format.

Number too large to parse as number?

hic — Mon, 19 Mar 2012 16:04:07 GMT

All of the above is correct. The simplest solution to the problem is to use Evaluate( F ) which will force an evaluation of a long string (or an expression).

HIC

Number too large to parse as number?

Mon, 19 Mar 2012 16:17:26 GMT

Thanks Henric,

That's a great idea - works perfectly!

regds

Simon

Number too large to parse as number?

erichshiino — Tue, 20 Mar 2012 21:24:39 GMT

Thanks!

It's great to know an alternative to that complicated workaround!

Regards,

Erich