Solved: Convert Bigint from MySQL to text in Qlikview - Qlik Community

balanandam · ‎2017-12-07

Hi,

I have data as below

7042394432384812206

1664224432861982307

6125752568775895263

8430717576788538022

1521929941711170694

6381227051209150169

620597394589890750

but when I add this column in qlikview report it converts to floating values

Pls. suggest.

Thanks in Advance!

Balanandam

balanandam · ‎2017-12-07

Hi All,

it must be CHAR format instead BIGINT, but you all know how does this change impacts on Production database. So, time being, I did this below

LOAD

SerialNumber;

SQL SELECT CAST(SerialNumber AS CHAR) from table;

Convert to CHAR before loading into Qlikview that solves my problem ....

Thanks!

Balanandam

View solution in original post

Anil_Babu_Samineni · ‎2017-12-07

What is the value indicates in MySQL??

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

balanandam · ‎2017-12-07

Anil, First set directly from MySQL.

Anil_Babu_Samineni · ‎2017-12-07

can you try with TEXT(FieldName) in Qlikview?

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

balanandam · ‎2017-12-07

I tried this, but no luck.

Anil_Babu_Samineni · ‎2017-12-07

can you tell me what does that field indicates? I mean to say, Which attribute business it is. This is 19 digits long key. So, then i would think much in detail if you provide the use case

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

balanandam · ‎2017-12-07

Oh! this field storing the data for serial number, ex: serial number of mobile, laptop and the MySQL column data type is Bigint.

Anil_Babu_Samineni · ‎2017-12-07

What are you getting? When you use this as field, does this return by any chance.. Because Qlikview doesn't have concept called Data Type as we call it as Limitations using Text(), Num() ...

Try this and show the image in list box/table box using FieldName and FieldName_BigIn

Left(FieldName,10) & Right(FieldName, 10) as FieldName_BigInt

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

jonathandienst · ‎2017-12-07

You can load them as text. This assumes that the ODBC driver supports bigint. If the ODBC driver is converting these to floats, then there is nothing you can do in Qlikview.

Qlikview supports numbers to a maximum of 14 or 15 digits, so an 18 digit number will be converted to floating point.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

sumit_ranjan_pa · ‎2017-12-07

This issue has been there since the beginning. QlikView has a restriction of 14 digits in the field. You cannot go more than that. As stated by jontydkpi‌ , it will be auto converted to float.

There are two ways of handling it. Handle this on the DB side and see if this can be stored as TEXT , than a number . I know this might be a huge impact on the DB, depending on the storage.

Second would be the choice , to break it down and store it as TEXT as suggested by loveisfail‌.