Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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?
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.
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
Here is a test case of your problem:
This tests the 3 main data types of Qlik: Number, Text, Dual.
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:
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.