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,

[rbecher]

Hi Andrey,

you need to cast the field on SQL statement level if it's not already a varchar field and also load with Text() into QlikView:

LOAD Text(ID_varchar) as ID .... ;

SELECT CAST(ID as varchar) as ID_varchar ... FROM ...;

- Ralf

[#3]

Hi Ralf,

Whow - thank you very much for such a quick response! I was struggling with the syntax, but managed to do it like so:

SELECT CONVERT("ID", SQL_CHAR) ..... FROM....;

For some reason CAST instead of CONVERT as well as other alternatives of SQL_CHAR (like SQL_WCHAR etc) were throwing up an error. I suspect this might have something to do with the connection driver used. Thank you for your help!

Andrey

[Anonymous]

Hi Jonas,

Did you find any workarounds for this, I am also facing the same problem.

Regards

Prince Anand

[Peter_Cammaert]

The workarounds are described in this thread by Jonathan and Ralf. Did you try them?

[Anonymous]

Hi Ralf,

Thanks It's working fine with my code.

SELECT CAST(ID as varchar) as ID_varchar

Regards

Prince Anand

[Vadim_Raschupkin]

LOAD
"id",
"id2";
SQL SELECT
"id",
CAST("id" AS varchar) as "id2"
FROM "Base";