topic Re: Convert Bigint from MySQL to text in Qlikview in QlikView

Convert Bigint from MySQL to text in Qlikview

balanandam — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Convert Bigint from MySQL to text in Qlikview

Anil_Babu_Samineni — Thu, 07 Dec 2017 05:33:49 GMT

What is the value indicates in MySQL??

Re: Convert Bigint from MySQL to text in Qlikview

balanandam — Thu, 07 Dec 2017 05:38:17 GMT

Anil, First set directly from MySQL.

Re: Convert Bigint from MySQL to text in Qlikview

Anil_Babu_Samineni — Thu, 07 Dec 2017 05:50:12 GMT

can you try with TEXT(FieldName) in Qlikview?

Re: Convert Bigint from MySQL to text in Qlikview

balanandam — Thu, 07 Dec 2017 05:52:05 GMT

I tried this, but no luck.

Re: Convert Bigint from MySQL to text in Qlikview

Anil_Babu_Samineni — Thu, 07 Dec 2017 05:54:01 GMT

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

Re: Convert Bigint from MySQL to text in Qlikview

balanandam — Thu, 07 Dec 2017 06:00:10 GMT

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

Re: Convert Bigint from MySQL to text in Qlikview

Anil_Babu_Samineni — Thu, 07 Dec 2017 06:05:40 GMT

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

Re: Convert Bigint from MySQL to text in Qlikview

jonathandienst — Thu, 07 Dec 2017 06:05:56 GMT

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.

Re: Convert Bigint from MySQL to text in Qlikview

sumit_ranjan_pa — Thu, 07 Dec 2017 06:43:05 GMT

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‌.

Re: Convert Bigint from MySQL to text in Qlikview

balanandam — Thu, 07 Dec 2017 07:08:47 GMT

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

Re: Convert Bigint from MySQL to text in Qlikview

Anil_Babu_Samineni — Thu, 07 Dec 2017 07:12:36 GMT

Great, Please close this thread by flag "correct answer" of your own response..

Re: Convert Bigint from MySQL to text in Qlikview

sumit_ranjan_pa — Thu, 07 Dec 2017 08:26:16 GMT

Thats a nice solution!