topic Re: Big numbers not loading as numbers in QlikView

Big numbers not loading as numbers

alerse — Fri, 07 Oct 2011 06:32:26 GMT

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

Big numbers not loading as numbers

swuehl — Fri, 07 Oct 2011 09:05:05 GMT

Hi,

I think there ist a comma missing between the two paths inside the if-function.

Just a typo in the posting?

Stefan

Big numbers not loading as numbers

alerse — Fri, 07 Oct 2011 09:07:48 GMT

Hi Stefan

Yes, sorry, just a typo in the post.

Big numbers not loading as numbers

swuehl — Fri, 07 Oct 2011 09:38:44 GMT

Is your longnum field aufgefüllt recognized as

numerical data type by QlikView?

Are you using an Oracle DB ?

Try using num#() resp. num() functions to parse in resp. format numerical values.

Stefan

Big numbers not loading as numbers

swuehl — Fri, 07 Oct 2011 09:41:46 GMT

Sorry, it was meant to be "successfully recognized"

This auto correction Is killing me ...

Re: Big numbers not loading as numbers

alerse — Fri, 07 Oct 2011 09:48:11 GMT

no problem

Ive tried that, it still passes it as a string to qlikview... My source is a normal comma seperated text file.

I will upload the document now...

Re: Big numbers not loading as numbers

alerse — Fri, 07 Oct 2011 10:46:55 GMT

ok, after some further investigation it looks like QlikView converts any number with more than 14 characters into text... Still no solution yet on how to get it numeric.

Big numbers not loading as numbers

Fri, 07 Oct 2011 13:50:32 GMT

Hi alerse,

I add a preceding LOAD to your script and lost these four larger values without any (error-)message.

LOAD

precision,

longnum,

longnum * 1 AS lostnum1,

num(longnum) AS lostnum

;

Is it a bug or a feature ?

Roland

Re: Big numbers not loading as numbers

SunilChauhan — Fri, 07 Oct 2011 14:21:17 GMT

see the attached file

hope this helps

Re: Big numbers not loading as numbers

alerse — Mon, 10 Oct 2011 11:57:15 GMT

Hi Sunil

Thanks for your help. UNfortunately that didnt work... If you just look at your new field it looks ok but if you bring in the longnum field you'll see we're missing the 4 long numbers...

Re: Big numbers not loading as numbers

swuehl — Mon, 10 Oct 2011 12:19:09 GMT

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

Re: Big numbers not loading as numbers

alerse — Mon, 10 Oct 2011 13:15:51 GMT

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

Re: Big numbers not loading as numbers

swuehl — Mon, 10 Oct 2011 13:30:52 GMT

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

Re: Big numbers not loading as numbers

alerse — Mon, 10 Oct 2011 13:34:12 GMT

perfect! Thank you so much Stefan!

Big numbers not loading as numbers

swuehl — Mon, 10 Oct 2011 13:38:58 GMT

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

Big numbers not loading as numbers

alerse — Tue, 11 Oct 2011 07:28:37 GMT

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

Big numbers not loading as numbers

swuehl — Tue, 11 Oct 2011 08:12:17 GMT

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?

Big numbers not loading as numbers

alerse — Tue, 11 Oct 2011 08:20:36 GMT

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?

Big numbers not loading as numbers

alerse — Tue, 11 Oct 2011 08:33:02 GMT

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

Big numbers not loading as numbers

swuehl — Tue, 11 Oct 2011 08:49:57 GMT

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