Solved: 19-digit character in SQL db - Qlik Community

gerhardl · ‎2016-08-10

Hi,

I have a 19-digit TRAN_ID with data type bigint in a SQL database. it might look like this: 2314885530818453536

When I load it into QlikView it ends up like this: 2.3148855308185e+018

I've had this before and got it to work by doing the below load method but this time it doesn't work and I don't understand why:

LOAD text(TRAN_ID) as TRAN_ID from XXX

SQL SELECT CAST(TRAN_ID as varchar) as TRAN_ID,*

I've also tried other options to varchar but it just refuses to work. What am I doing wrong?

gerhardl · ‎2016-08-11

Okay, I got this right by accident, and I still don't know WHY this fix works, but it does.

I had this in my SQL select statement, which then returned 2.3148855308185e+018:

SQL SELECT cast(TRAN_ID as varchar) as TRAN_ID, *

I then changed it to this, and then it works and returns 2314885530818453536

SQL SELECT *, cast(TRAN_ID as varchar) as TRAN_ID

So literally the only "fix" was to move the asterisk which selects all the other fields to the front of the CAST function. When the CAST function comes first it for some reason doesn't work properly.

Makes no sense (to me).

View solution in original post

Anonymous · ‎2016-08-10

Hi

try this ?

LOAD NUM((TRAN_ID),'###################') as TRAN_ID from XXX

SQL SELECT CAST(TRAN_ID as varchar) as TRAN_ID,*

gerhardl · ‎2016-08-10

Hi,

Doesn't work - everything then changes to 9223372036854780000

The last few digits are being dropped.

marcus_sommer · ‎2016-08-10

AFAIK could qlikview not handle such big numbers else only until 14 digits. But to load them as text should work, maybe within a preceeding load on top your sql, like:

Load *, text(TRAN_ID) as TRAN_ID_TEXT;

SQL SELECT * From Source;

- Marcus

maxgro · ‎2016-08-10

in the load, try to keep the TRAN_ID as a text and convert to char in the SQL part

LOAD

TRAN_ID,

...............

;

SQL

SELECT

CAST(TRAN_ID as varchar) as TRAN_ID,

*

FROM

.......................

vishsaggi · ‎2016-08-10

Did you try this just wondering ?

LOAD *,

Num#(TRAN_ID) AS Tran_ID

.....;

Thanks,
V.

gerhardl · ‎2016-08-11

Hi,

This is what I've tried already, as posted in my question originally.

tried with and without casting in SQL select statement

gerhardl · ‎2016-08-11

Doesn't work.

gerhardl · ‎2016-08-11

Yes, but doesn't work.

marcus_sommer · ‎2016-08-11

Maybe not the most elegant approaches but two different workarounds which should work:

Load

*,

text(keepchar(TRAN_ID_2, '0123456789')) as TRAN_ID_TEXT

text(TRAN_ID_PART_1 & TRAN_ID_PART_2) as TRAN_ID_TEXT_2;

SQL SELECT

*,

'#' & TRAN_ID & '#' as TRAN_ID_2,

mid(TRAN_ID, 1, 9) as TRAN_ID_PART_1,

mid(TRAN_ID, 10) as TRAN_ID_PART_2

From Source;

- Marcus