Text to number Issue

[marcel_olmo]

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.

[Anonymous]

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','.'))

[swuehl · Accepted solution]

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.

[marcel_olmo]

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.