Qlikview 18 character limit problem with large numbers

[saxjonas]

Hi all.

In Qlikview 10 there seem to have been made some changes from 8.5 when it comes to displaying large numbers.

Before it was possible to have large numbers over 20 characters long to be displayed "as is". Now, however, there seems to be a limit of 18 characters and everything above that will be displayed as a exponential decimal number (eg. 1,3442356788422E13)

What the customer wants to do is display the number like "13442356788422" instead of "1,3442356788422E13". To further complicate this, I've seen that in many cases it depends on how the numbers are stored in the SQL database. In some cases you can simply load it with text() at will show properly but in other databases it will show exponential numbers anyway.

Are there any possible workarounds for this?

Thanks in advance,

[jonathandienst]

Jonas

I have not tried this, but what about splitting the number on loading with the Mod() and Div() functions?

Something like

LOAD Div(longNum, 1e10) & Mod(longNum, 1e10) As aLongNumber, ....

(Assumes number is less than 20 characters, and a whole number.) You will get a leading zero if the number is less than the Div parameter. Of course, if it is coming in as exponential from the ODBC driver, this may not solve the problem.

Jonathan

[saxjonas]

Thanks for your reply Jonathan!

I've asked them to change the fields in the SQL database to TEXT instead of DIGIT or INT. That should probably solve the issue.

I'll let you know if your solution worked out!

/Jonas

[#4]

Hi All,

I am also facing same issue while converting 15 didit long numner. I have applied Jonathan formula but not getting correct output.applied Div and Mod formul is giveng 14 digit number only.

Can any one help on this.

=Div(650000205289410, 1e10) & Mod(650000205289410, 1e10) getting o/p  65000205289410

[rbecher]

Hi Pradeep,

this is because the result of the Mod() function needs a string left padding with '0' like this (just consider a number like this 650000000000001):

=Div(650000205289410, 1e10) & left(repeat('0',10), 10 - len(text(Mod(650000205289410, 1e10)))) & Mod(650000205289410, 1e10)

- Ralf

[#6]

Hi Ralf,

Thanks for your update.

Will this logic work for all kind of long value i.e 1 or 20 digit?

Also can we use below one

=Div(650000205289410, 1000000000) & Mod(650000205289410, 1000000000)

[rbecher]

Should work..

[ChristofSchwarz]

Are those large numbers, which you are planning to load from an SQL table, going to be used as numbers (and mathematical operations are going to be used on such) in QlikView or are those just long IDs, which can be considered text? If it is text, you need to do the conversion into text already in the SQL statement, so that over the driver (OLE or ODBC) those arrive already as text. If not, the "Text()" function in QlikView load script comes too late, the driver has already delivered a number.

QlikView's precision of a number cannot exceed 18 digits. This is the point, where it (internally) starts to use the E+nnn representation. In other words,

QlikView would consider the following as true: 11111111111111111199 = 11111111111111111111 because there is no more precision behind the numbers than 18 digits. However as strings, the comparision '11111111111111111199' = '11111111111111111111' is certainly false.

I suspect you like to get ID fields, so do something like CAST(....) or CSTR( ... ) or add a character in SQL.

[ali_hijazi]

I have the following number:15688946100202001 your function combination returns 15688946100202000  needs modification please advise

[#10]

Hi Christof,

Thank you for info. This is exactly the problem I've got. The field is question is up to 20 characters long (all integers) and it's indeed an ID (as well as key) field.

Please could you elaborate on use of CAST(....) or CSTR( ... )? How would I implement that?

Thank you in advance!