Load from QVD gives different values compared to source db table

[happydays1967]

Hi,

I hope somebody here can help me. I am re-writing a QS application to use QVD files in stead of reading directly from the database. I come across some very strange behaviour while doing that. Probably the strangest is the following. I have a table in an Oracle 11 database. I have a field called 'GEOGRAPHY_CODE' which has datatype VARCHAR2(200 Char) at database level. There are 67 rows for the specific geography I am looking for with values 01 - 66 + 99. When I query the database it returns 67 rows with for all of them 2 character values (which are numeric chars), as it should be. However, when I store this table into a QVD and then load from that QVD, I get 67 rows as expected, but some of the values are all of a sudden 3 characters in stead of 2. But not all of them..... I cannot find any logic in this.

Among the same lines, the tables I am using have ID fields (all NUMBER(16) on database level). When in the LOAD from the QVD I match this field using the WildMatch() function, for some tables I get a match (no matter whether I use a constant or a variable in the function), and for others I don't, whereas on database level (and even visually) the field contains the exact same value as the value I am matching against.

As it seems something is not working as I would expect it to. Anybody got any thoughts on this?

Thanks in advance!

HP

[krishna_2644]

can you post some screenshots that might be helpful to understand more clearly?

[happydays1967]

Maybe this clarifies issue 1 a bit more.... But I believe they are related and have something to do with how Qlik stores/retrieves data from QVD:

Thanks,

HP

[marcus_sommer]

I suggest that you used a Preceding Load to load from the database and storing this result as QVD. This meant something like:

Geography:

load Type, text(Code) as Code;

select * from Geography where ....;

store Geography into Geography.qvd (qvd);

drop tables Geography;

Geography:

load * from Geography.qvd (qvd);

and you should get a consistent output respectively the data like they are transferred from the database.

- Marcus

[happydays1967]

Marcus,

I have read the article, and see the point. However, if I have to do this for every field in every table I load, it would make the script a bit complicated..... I would expect Qlik to correctly evaluate the contents of a field and not, out of nowhere, add data to it with no obvious reason, and inconsistent as well....

On top of that, I have tried the text(geography_code) and it gives the same result. I could try this while selecting from db before storing, but again, it is a dynamic QVD extractor, that loops through a lot of tables, that contain lots of fields. Having to do a transform on every field is (near to) impossible.

HP

[marcus_sommer]

In my experience the loading and interpreting of data worked in Qlik usually quite well and it's rather seldom that there such difficulties. Therefore I believe it will work with the most of your fields without the need to implement a special treatment within the extraction.

Further to implement a few exceptions within the extraction-routine or maybe to extend the logics that all fields will be loaded as strings or even with a check on the values respectively with an appropriate format-mapping to define the treatment of the fields individually is possible and shouldn't be a real shopstopper.

Beside this I'm not convinced yet that the problem is really on the qlik-side. Are you absolutely sure that the values within the database are like they are displayed? The visual output could related to formattings be different from the values behind it. Another possible error-source could be the database-driver.

After all it meant I would check which data are transferred into Qlik probably by adding something like this to the sql query:

...

len(code) as CodeLen

mid(code, 1, 1) as Char1,

ord(mid(code, 1, 1)) as CharNumber1,

mid(code, 2, 1) as Char2,

ord(mid(code, 2, 1)) as CharNumber2,

mid(code, 3, 1) as Char3,

ord(mid(code, 3, 1)) as CharNumber3

...

Ord() could be called different within your database.

- Marcus

[happydays1967]

In the mean time, I have done some checking with your suggestions. And yes, I have experienced something like this before. The data is definitely correct in the database, as different (query) tools show the same results. When loading the check:

and then using this converted field in the visualisation it does show up correctly:

This leads me to beleive it is an issue with how Qlik stores values in the QVD. Because when I explicitely convert to text when loading from QVD it still shows incorrect. Besides that, explicit conversion would be possible querying the system tables in the database and building a load statement based on that, but that is a lot of development and a workaround for what I consider a bug in Qlik...

HP

[marcus_sommer]

Is this the first load within the application where the field GEOGRAPHY_CODE is loaded or are there already other tables which loads this field?

- Marcus

[happydays1967]

Marcus,

In my test from which the screenprint above is made, this is the first and only load of the field/table. So, no, it does not come from another table where different formatting might be applied

HP

[marcus_sommer]

Please do the following twice - one time in an empty application and one time directly below the sql-query without using the preceeding approach and storing it to a qvd (just to be sure):

load * from Geography.qvd (qvd);

load

     len(fieldvalue('GEOGRAPHY_CODE', recno())) as Len,

     text(fieldvalue('GEOGRAPHY_CODE', recno())) as String,

     num(fieldvalue('GEOGRAPHY_CODE', recno())) as Number,

     mid(fieldvalue('GEOGRAPHY_CODE', recno()), iterno(), 1) as Char,

     ord(mid(fieldvalue('GEOGRAPHY_CODE', recno()), iterno(), 1)) as CharNumber

autogenerate fieldvaluecount('GEOGRAPHY_CODE')

while iterno() <= len(fieldvalue('GEOGRAPHY_CODE', recno()));

and display it as table.

- Marcus