topic Re: Large Integer formatting question in QlikView

Large Integer formatting question

mlandsittel — Tue, 18 Feb 2014 18:08:41 GMT

New to QlikView and working with a database that I cannot change. Many of the fields I need to use for table linking are large integers. In the database (Sybase) the field looks like: 8826756202483417459. As it comes into QlikVIew it will look something like 8.0675375980436e+018. The formatting throws all linking off.

I have tried text(), I get a text value of the scientific notation. I have tried num(field,'####################') but I am getting a number that appears to be rounded.

Not really sure how to address this w/in QlikView. Any help would be greatly appreciated!

Re: Large Integer formatting question

Gysbert_Wassenaar — Tue, 18 Feb 2014 18:11:21 GMT

Qlikview knows only one numeric data type, the 64-bit IEEE float which can only show 14 digits. You can try loading it as text. See this discussion: number of 17 digits

Re: Large Integer formatting question

Nicole-Smith — Tue, 18 Feb 2014 18:13:58 GMT

There are also ideas out there for supporting more than 14 digits that you can vote up:

http://community.qlik.com/ideas/3089

Re: Large Integer formatting question

hic — Tue, 18 Feb 2014 21:10:37 GMT

If the Text() function returns the scientific notation as text, then I would guess that your ODBC driver/OLEDB provider does the rounding to 64-bit IEEE number and returns it as scientific notation. If it had been coming to QlikView as a string the Text() would not return scientific notation.

HIC

Re: Large Integer formatting question

mlandsittel — Tue, 18 Feb 2014 21:47:01 GMT

Understood - is there anything in Qlik that would be similar to a cast function. I expected this problem based on experience with Crystal Reports-there I could use a cast("Table"."Field" as varchar) function - then join tables based on that.

Re: Large Integer formatting question

hic — Tue, 18 Feb 2014 22:02:18 GMT

There are no data types in QlikView, and consequently there is not cast function. But I think you can use any of the following to create unique keys:

Autonumber(field)

Hash256(field)

Left(field,14)

as long as you use the same function in both tables that have this key...

An alternative is to use the fact that the SELECT statement is evaluated by the DB, so if you make some transformation (similar to the ones above) inside the SELECT instead of inside the Load, you may be able to use a larger mantissa than what QlikView can see.

HIC

Re: Large Integer formatting question

Tue, 18 Feb 2014 22:10:38 GMT

Hi Michael,

I suggest you to use the Evaluate() function instead of the Num() function.

Something like this:

Load

Evaluate(Column1) as Column;

SQL

Select Column1

from Table1;

I hope this helps you

Re: Large Integer formatting question

flanfranco — Mon, 02 Mar 2015 17:24:30 GMT

I'm faced same problem, i resolve it in the ETL (SQL SELECT), retrieving the INTEGER field in VARCHAR, for example:

example_table:

SQL SELECT *,

convert(varchar(), example_field_integer) AS Example_Field_string

FROM example.dbo."example_table";

STORE example_table into $(VPath)\ETL\QVDs\example_table.qvd (qvd);

DROP Table example_table;

I hope help you!

Greetings!

Flavio