Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
alerse
Contributor II
Contributor II

Big numbers not loading as numbers

Hi

I've got a very strange problem. One of my data sources have quite a large number in it, lets call it longNum (10735136080000000.000000). My load script is this: (precision can be anything from 0 to 9)

if(precision = 0,longNum,longNum/(pow(10,precision))) as field1,

In QlikView field1 is now empty... Any idea how to fix this? I can bring in the field as a string but as soon as I try to do the division it breaks again.

Your help will be appreciated!!

19 Replies
swuehl
MVP
MVP

Hi,

please look at attached.

This is only a somewhat dirty workaround, splitting the strings in two parts and combining them afterwards to numbers again.

I haven't found another solution yet.

Hope it helps,

Stefan

alerse
Contributor II
Contributor II
Author

Wow Stefan! Nice work!! I thought about a solution like this but my maths skills stopped me from trying it

One small thing that I can't seem to sort out... The end result is duplicating the 10th decimal... Where the longnum is 10735136080000000.123 the field1 result is 10735136.088.

Another example:

longnum:  11763344429850000.00

field1:       1.1763344.22985

swuehl
MVP
MVP

You are right, the calculation of newValue seems to be incorrect with regard to adding the right / mid part.

Change the part using the mid function to

mid(floorlongnum,11)

(before, we started with 10, that duplicated the value).

Regards,

Stefan

alerse
Contributor II
Contributor II
Author

perfect! Thank you so much Stefan!

swuehl
MVP
MVP

You're welcome.

One more thing, you don't need the many preceding loads of course, you might want to incorporate the terms into one load, but that should be quite easy (I preferred the multiply loads just for debugging).

Regards,

Stefan

alerse
Contributor II
Contributor II
Author

Hi Stefan

Just implemented this and Im getting a very weird error... When you add the longnum 9920334320400000.000000 (and precision 9) you'll see the field1 value of 1330399.7

It should be 9920334.3204...

swuehl
MVP
MVP

Another interesting thing...

It seems to work if you increase the threshold index, the position index where we split the string, change it from 10 to 14 like in

if( len(floorlongnum) > 14, num#(left(floorlongnum,14))*pow(10,len(floorlongnum)-14)+num#(mid(floorlongnum,15)),longnum) as newValue;

(and accordingly at the other places, too).

Not sure why it is not working.

Another thing, maybe it is better to do the whole thing (division to given precision) completely with strings? I noticed that on large numbers, we probably get some digits lost at the very end.

What do you think?

alerse
Contributor II
Contributor II
Author

Thanks again Stafan!

You're right, after a quick test the splitting of the string at 14 works 100%... I'll do a few more tests.

I can try to do it with strings... Im not to worried about the accuracy down to the nth degee... How to you cast the number as a string?

alerse
Contributor II
Contributor II
Author

looking at your code again... We're not using the field fraclongnum. Maybe that will fix the digits lost at the end?

swuehl
MVP
MVP

I think you could do that with text() function.

Try this

LOAD

longnum,

precision,

floorlongnum,

fraclongnum,

floorlongnum+fraclongnum as newValue;

LOAD

longnum,

precision,

num#(left(text(longnum),index(text(longnum),'.')-1-precision)) as floorlongnum,

num#('.'&keepchar(mid(text(longnum),index(text(longnum),'.')-precision),'0123456789')) as fraclongnum

resident Input;

This is doing the division completely in string domain (using left function). Should work quite nicely if your precision is smaller than the number of characters left from the decimal point.

Regards,

Stefan