Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Hi Gerhard,
try this i am not sure,
=Text(Train ID) in dimension Expression
Thanks
I tried on a sql server db and it works, see the image with sql server (top) and qlik result (bottom)
T:
LOAD text(TRAN_ID) as TRAN_ID;
SQL SELECT
cast(TRAN_ID as varchar) as TRAN_ID
FROM "C4PLATINUM_BENZINE".dbo."Table_1";
T:
LOAD text(TRAN_ID) as TRAN_ID;
SQL SELECT
convert(varchar, TRAN_ID) as TRAN_ID
FROM "C4PLATINUM_BENZINE".dbo."Table_1";
Doesn't work for me.
ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Argument data type bigint is invalid for argument 1 of substring function.
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).
I think it doesn't because you use select *
This works for me (without select * but with other fields)
It seems the select * has a side effect on cast(....), I think because with select * you extract twice the TRAN_ID
T:
LOAD text(TRAN_ID) as TRAN_ID, col1, col2;
SQL SELECT
col1, col2, cast(TRAN_ID as varchar) as TRAN_ID
FROM "Table_1" t;
and also this
SQL SELECT
cast(TRAN_ID as varchar) as TRAN_ID, col1, col2
FROM "C4PLATINUM_BENZINE".dbo."Table_1" t;