11 Replies Latest reply: Jun 1, 2018 1:51 AM by Henric Cronström

Problem with big numbers (more than 14 digits)

Hi guys ( stalwar1, hic , petter-s , mto  ..)

I have a problem to load BIG NUMBERS (more than 14,15 and even 17 digits).

In fact, when It's more than 14, Qlik can't seem to recognize the numbers as numbers anymore.

and this seemed to work at first sight. In fact, the numbers that were imported as Zero are now being well interpreted but then I've found out that there are still some numbers imported as 0 (even bigger numbers) ! So the Evaluate() doesn't seem to work for all the cases...

The question is: how can I import all the numbers (whatever how BIIIIIG they are), and still load them as numbers??)

ps: My source is REST; thus, I'd have to handle things within Qlik.

Thanks !

• Re: Problem with big numbers (more than 14 digits)

Great !

• Re: Problem with big numbers (more than 14 digits)

Qlik Sense can't seem to recognize the 1e10 part !

see:

• Re: Problem with big numbers (more than 14 digits)

Throws an error when you run it? Use 10000000000 instead of 1E10 I guess

• Re: Problem with big numbers (more than 14 digits)

does not solve the problem:

see, this is only one CDR..

1) using evaluate (on the aggregated notionals measure)

2) Using the expression u've shared with us:

Qlik can't recognize big numbers using it; thus he import them as 0; which explain the difference

• Re: Problem with big numbers (more than 14 digits)

Are you summing the numbers or counting them?

• Re: Problem with big numbers (more than 14 digits)

summing sunny;

this CDR is composed of 5 lines; one line contains a 14 digits numbers (the 18 .. numbers shown in the screenshot and imported as so); while the 4 other contain 15 digits numbers; these 15 digits numbers are all imported as 0 using this :

LOAD Div(Field, 1e10) & left(repeat('0',10), 10 - len(text(Mod(Field, 1e10)))) & Mod(Field, 1e10) as MyNum

while they were imported correctly using evaluate().

• Re: Problem with big numbers (more than 14 digits)

I think Ralf's formula is converting the number into text which is why I don't think you can sum it.... only thing I can think about is to divide the number for 1000 or 10000 may be...or may be Henric Cronström can give you better idea.

• Re: Problem with big numbers (more than 14 digits)

Evaluate() should be able to evaluate a string as a number no matter how large the number is. So if this fails, there is something else that is wrong. Can you post an example where it fails?

It is important, however, to understand that the Qlik engine internally stores numbers using the 64-bit binary data type, which means only 14-15 valid decimal digits. In other words: Evaluate() will round the number to something with 14-15 valid digits.

HIC

• Re: Problem with big numbers (more than 14 digits)

Thank you for ur time; I'd have a question though:

what if the exact numbers is 16 digits and must be kept that way? How should we proceed?

• Re: Problem with big numbers (more than 14 digits)

If you want all 16 digits, and treat it like text, you can load it as

Text(Field)

but then you cannot use it as a number.

You can also load it using

Dual(Field, Evaluate(Field))

Then you will get the original string (all 16 digits) as the textual value, and the evaluated string as numeric value. This works fine as long as you don't have two field values with identical 15 digits, but different 16th digit.

HIC