topic Re: MySQL Longtext Data Type in QlikView

MySQL Longtext Data Type

darrellbutler — Mon, 25 Sep 2017 13:43:47 GMT

Hi, I'm pulling data from a MySQL database. Several fields are of the type LONGTEXT, when creating a QVD from these extracts the contents of these fields look like a hexadecimal representation of the textual data. The original MySQL field length can be up to 1k characters.

I need to see the actual text as it sits in the source database, when it comes into Qlikview it makes no sense. If anybody has any tips on making this data readable I'd be keen to hear from you.

Many thanks

Darrell

Re: MySQL Longtext Data Type

Anil_Babu_Samineni — Mon, 25 Sep 2017 13:45:40 GMT

May be use Text(FieldName) as Field..

Re: MySQL Longtext Data Type

darrellbutler — Mon, 25 Sep 2017 13:53:30 GMT

Thanks Anil, I tried that but it didn't work !

Re: MySQL Longtext Data Type

Anil_Babu_Samineni — Mon, 25 Sep 2017 13:55:13 GMT

Can you show the image in MySQL and Qlikview after load the values?

Re: MySQL Longtext Data Type

darrellbutler — Mon, 25 Sep 2017 14:05:37 GMT

Hi Anil, on the left we have the field, "title" when loaded into QV12 and on the right is the source data.

Re: MySQL Longtext Data Type

Colin-Albert — Mon, 25 Sep 2017 14:06:34 GMT

I think you will have to use CAST to change the data from Longtext to char or varchar, specifying the maximum number of characters.

cast(Fieldname as varchar(255))

Note - Cast is a SQL function so will need to be done in the sql part of the script not the QlikView section of the load.

Re: MySQL Longtext Data Type

Anil_Babu_Samineni — Mon, 25 Sep 2017 14:10:03 GMT

How you are created the table. Can you show the table struture and definition of table. I am assuming you may need to change from byte to Character using Cast..

Re: MySQL Longtext Data Type

darrellbutler — Mon, 25 Sep 2017 15:08:13 GMT

Many thanks guys, I had to use Convert as I think we're on an older version of MySQL and cast isn't supported. It appears to have worked can I both give you correct answer status ?

Re: MySQL Longtext Data Type

Colin-Albert — Mon, 25 Sep 2017 15:13:42 GMT

Its only 1 correct and numerous helpfuls.