topic Re: Text to number Issue in QlikView

Text to number Issue

marcel_olmo — Mon, 22 Jun 2015 16:15:36 GMT

Hi guys,

I have a number field populated as a text as following :

Quantity

11.000.000.000.000.000.000.000

12.000.000.000.000.000.000.000

14.000.000.000.000.000.000.000

When I load that field into Qlikview, it's recognized as a text, and I can't make any operation with that. I've tried formatting it with num(), num#(), replacing dots per commas or nothing, with no exit.

Anyone knows how to do it right?

Many thanks in advance.

Regards, Marcel.

Re: Text to number Issue

Anonymous — Mon, 22 Jun 2015 16:22:50 GMT

That is 23 digits which is a lot and I have feeling more digits than QlikView can handle.

With just 14 digits this works though :

=num(purgechar('11.000.000.000.000','.'))

Re: Text to number Issue

swuehl — Mon, 22 Jun 2015 16:29:17 GMT

Yes, you are hitting the max number of integer digits limit (I also believe it's 14 digits).

You can try loading the first few significant digits, then multiply by 1E21, something like

=subfield('11.000.000.000.000.000.000.000','.',1) * 1E21

implying a cast to floating point representation.

Re: Text to number Issue

marcel_olmo — Mon, 22 Jun 2015 16:36:24 GMT

Thanks guys for answering so quickly,

the maximum precission number that accepts is 18 digit, so I did this to make it work :

num(purgechar(left(Quantity,18),'.')) as Quantity

Regards, Marcel.