Big numbers not loading as numbers

[alerse]

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!!

[swuehl]

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]

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 · Accepted solution]

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]

perfect! Thank you so much Stefan!

[swuehl]

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]

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]

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]

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]

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

[swuehl]

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