MySQL Longtext Data Type

[darrellbutler]

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

[Anil_Babu_Samineni]

May be use Text(FieldName) as Field..

[darrellbutler]

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

[Anil_Babu_Samineni]

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

[darrellbutler]

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

[Colin-Albert · Accepted solution]

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.

[Anil_Babu_Samineni]

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

[darrellbutler]

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 ?

[Colin-Albert]

Its only 1 correct and numerous helpfuls.