Large Integer formatting question

[mlandsittel]

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!

[Gysbert_Wassenaar]

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

[Nicole-Smith]

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

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

[hic]

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

[mlandsittel]

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.

[hic]

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

[#7]

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

[flanfranco]

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