Problem with big numbers (more than 14 digits)

[OmarBenSalem]

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.

I've tried to surround the field by evaluate when loading it (evaluate(Field) instead of Field)

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 !

[sunny_talwar]

Check here

https://gist.github.com/ralfbecher/6550467

[hic]

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

[OmarBenSalem]

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?

[OmarBenSalem]

Great !

[OmarBenSalem]

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

see:

[sunny_talwar]

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

[OmarBenSalem]

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

[sunny_talwar]

Are you summing the numbers or counting them?

[OmarBenSalem]

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().