Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

fortaleza40
New Contributor II

Chacter varing not stores in QVD

Hello,

I am storing data in a QVD file coming from a postgress database, this data is more of 200 characters and the field where is stored is Character Varing, the data is not being stored in the QVD field, but when i change the data type to text it is stored, so i have a question about if exist some kind the limitation in the number of characters that a QVD file can store in a character varing field and if it is configurable or by default.

5 Replies
Partner
Partner

Re: Chacter varing not stores in QVD

Can you provide some supporting content for this question? It will be hard for us to give you a good answer without specifics.

Qlik does not have a Character Varying field, so I am assuming you mean the database field data type. Qlik will consume whatever is being passed through the database driver. At that point, Qlik doesn't really care what the data type is, which is usually not a problem for varchar fields. Once you use a Load statement or the application finishes the load, it will try to assign a data type: Numeric, Text, or Dual. There are a couple other tags that pertain to geo, but that's it. 

A couple things you can check:

Are you using a LOAD statement after the SQL query?

Data:
Load
    VarCharField
;
SQL SELECT
    VarCharField
FROM
    DBTable;

vs

Data:
SQL SELECT
    VarCharField
FROM
    DBTable;

 

If you do not perform the LOAD statement, Qlik will not interpret the field before storing to QVD.

If you are, you can try wrapping the Qlik field with the Text function.

 

Data:
Load
    Text(VarCharField) as VarCharField
;
SQL SELECT
    VarCharField
FROM
    DBTable;

If you want both data types, then you can do something like this:

 

Data:
Load
    Alt(VarCharField,Text(VarCharField)) as VarCharField
;
SQL SELECT
    VarCharField
FROM
    DBTable;

 

 

Blog: WhereClause   Twitter: @treysmithdev
Highlighted
fortaleza40
New Contributor II

Re: Chacter varing not stores in QVD

The load statement after SQL query,

what i found is that if the data type in the database is character varing the space to store data in the QVD field is less that if the field is text, and if the data type in database is chacarter varing and the data more of 2000 characters the data is not stored in the QVD, but if the data type is text, yes, it is stored not matter 2000 characters long, so i suppose that yes, the data type matters when Qlik store data in QVD.

The question is, this is a bug in our system or it is an standers of size limit by data type?

Partner
Partner

Re: Chacter varing not stores in QVD

Qlik does not have a character varying data type, so I do not think that is the problem. 

Your problem is probably either with the ODBC/OLEDB driver not passing all of the data or an interpretation issue with the field once it is in Qlik.

Blog: WhereClause   Twitter: @treysmithdev
fortaleza40
New Contributor II

Re: Chacter varing not stores in QVD

I tested that situation, get the data from SQL and store in a temp table, TempTable:, after loop all the rows and check if field with 2000 characteres was there and yes it was there, so by the moment my solution was do a left to decrease the number of characters before the store sentence, will like to know the root cause of this issue, but as you say there is not character varing i am confused, why if it is text data type all is stored, even you open the QVD and the field is more long too, there is a configuration file or panel to QVD generation?

 

Thanks Trey for your replies

Partner
Partner

Re: Chacter varing not stores in QVD

Here is a test case of your problem:

This tests the 3 main data types of Qlik: Number, Text, Dual. 

  • Test 1: Non-specified string field of 3000 characters
  • Test 2: Specified field of 3000 characters
  • Test 3: Dual field (String display, Numeric Value) of 3000 characters
  • Test 4: Field containing both Numeric values and Text values with 3000 characters.

Description:

Load table of varying test cases to represent possible field values. Export to QVD. Reload QVD and check data is still there and the max length of the field.

 

data:
Load
    Num(Number) 							as Number,
    String 									as String,
	Text(String) 							as TextString,
    Dual(String,Number) 					as DualField,
    If(Mod(RowNo(),2) = 0, String, Number) 	as MixedField,
;
Load
	RecNo() * (Rand() * 1000000) 			as Number,
	Repeat('ABCEFGHIJK',300) 				as String
AutoGenerate
	(100);
    
    
    
Store data into [lib://QlikShare/Dev/Test/qvd-field-length.qvd] (qvd);



Drop table data;

data_qvd:
Load * FROM [lib://QlikShare/Dev/Test/qvd-field-length.qvd] (qvd);



data_metrics:
Load
	'' as Field,
	Num(Null()) as Length
AutoGenerate
	(0);
    
For f = 0 to NoOfFields('data_qvd')-1

	let vField = FieldName($(f)+1,'data_qvd');
    
	Concatenate(data_metrics)
    Load
    	'$(vField)' as Field,
    	Max(Length) as Length
    ;
    Load
    	Len($(vField)) as Length
    Resident
    	data_qvd;

Next f;

 

 

Which results in:

clipboard_image_0.png

 

So what this shows is that there is not a character limit or an issue within the field type of a QVD. 

 

It might be worth providing some example code on your experience because I do not think the issue is with the QVD step.

Blog: WhereClause   Twitter: @treysmithdev