topic Re: Load from QVD gives different values compared to source db table in Connectivity & Data Prep

Load from QVD gives different values compared to source db table

happydays1967 — Thu, 12 Apr 2018 14:46:42 GMT

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!

Re: Load from QVD gives different values compared to source db table

Qrishna — Thu, 12 Apr 2018 16:12:32 GMT

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

Re: Load from QVD gives different values compared to source db table

happydays1967 — Fri, 13 Apr 2018 06:43:19 GMT

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,

Re: Load from QVD gives different values compared to source db table

marcus_sommer — Fri, 13 Apr 2018 07:07:30 GMT

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

Re: Load from QVD gives different values compared to source db table

happydays1967 — Fri, 13 Apr 2018 07:19:34 GMT

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.

Re: Load from QVD gives different values compared to source db table

marcus_sommer — Fri, 13 Apr 2018 07:59:28 GMT

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

Re: Load from QVD gives different values compared to source db table

happydays1967 — Fri, 13 Apr 2018 08:08:27 GMT

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

Re: Load from QVD gives different values compared to source db table

marcus_sommer — Fri, 13 Apr 2018 08:31:41 GMT

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

Re: Load from QVD gives different values compared to source db table

happydays1967 — Fri, 13 Apr 2018 08:38:26 GMT

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

Re: Load from QVD gives different values compared to source db table

marcus_sommer — Fri, 13 Apr 2018 11:30:21 GMT

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

Re: Load from QVD gives different values compared to source db table

happydays1967 — Mon, 16 Apr 2018 09:06:49 GMT

Marcus,

Thanks for thinking along. I have made a little change to you script to be able to better check some things. Output is surprising, to say the least. It does not seem to be a problem in the QVD storage, but with dataretrieval/display in QS. When I retrieve the same data from db (tried OLE, ODBC, Microsoft driver, Oracle driver) or qvd, the same results are displayed. It seems values are 'confused' between records. Let me try to explain the structure of the data and the issue a bit more in depth. We have 3 geography levels, of which the codes are partially overlapping, partially similar. The codes are numeric values, stored as varchar in the database. When querying the database directly, the codes are displayed properly:

It seems that the GEO1 and GEO2 codes are mixed up in QS, but strangely enough, not for all values:

The wierdest thing is that QS reports the field as 3 positions for the faulty values, but then iterates only 2 chars in the script Marcus gave me.

When explicitely converting datatypes while loading and storing the QVD, the results from teh load from the QVD are displayed correctly:

Because it is explicitely converted to text, apparently QS can't translate it back to a number, which is fine in this case.

I have found a way to automatically explicitely concert types by building a load statement based on the ALL_TAB_COLUMS system table in Oracle, however, this remains a workaround, and I consider filing this as a bug (if only I knew how and where ).

TmpCols:

SQL SELECT COLUMN_NAME,DATA_TYPE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = '$(sTableName)';

LET z = NoOfRows('TmpCols')-1;

SET sLoad = 'LOAD ';

For i = 0 to $(z)

LET sColName = peek('COLUMN_NAME',$(i),'TmpCols');

LET sDataType = peek('DATA_TYPE',$(i),'TmpCols');

LET sLoad = sLoad & if(match(sDataType,'DATE','TIMESTAMP(6)','TIMESTAMP(6) WITH LOCAL TIME ZONE') > 0,'date(',

if(wildmatch(sDataType,'NUMBER*') > 0,'num(','text(')) & sColName & ') as ' & sColName & ', ';

LET sLoad = mid(sLoad,1,len(sLoad)-2) & ';';

Tmp:

$(sLoad);

SQL SELECT * FROM $(sTableName) WHERE

.....;

STORE * from Tmp INTO '$(sQVDTrgFolder)$(sFilePrefix)NEXXUSMIDEMO_GEOGRAPHY.QVD' (qvd);

DROP TABLE Tmp;

It will slow down the QVD creation because we have to loop through every column of every table, but at least the results are correct....

HP !

Re: Load from QVD gives different values compared to source db table

marcus_sommer — Mon, 16 Apr 2018 12:26:35 GMT

I think it's a good solution to ensure that each field could be loaded and stored as it should. Especially as I wouldn't expect a quite significantly increasing of the load-times because querying the fieldnames and formattings from the db systemtables and building a load-statement on the fly within the loop shouldn't be take very long. Also the additionally transformations within the preceeding load shouldn't add a significantly amount of time.

Like above mentioned with the most fields there shouldn't be problems by the data-interpretation even or just because Qlik has no datatypes only numeric- or string-fields respectively mixed fields. Problems occur if there are leading spaces or zeros or similar (I know that leading + or - signs lead to a numeric interpretation and probably there are further cases).

Essential how Qlik interprets the data is the first loaded value of each field. This meant adjusting the load-order either within the load or maybe in a foregoing dummy-load will have an effect. You could comprehend it with the following example:

t0:

load text('abc') as F1 autogenerate 1;

t1:

load *, rowno() as RowNo inline [

F1, F2

1, 1

01, 01

001, 001

];

drop tables t0;

which leads (in QlikView but AFAIK Sense should be the same) to this result:

If an approach like this one worked in your case and there aren't too many "problematic" fields you could consider to skip your load-on-the-fly logic and just handle these fields within the application with such a "special" treatment.

- Marcus

Re: Load from QVD gives different values compared to source db table

happydays1967 — Tue, 17 Apr 2018 09:45:20 GMT

marcus_sommer‌ Thank you for all your time and suggestions! The solution with querying the system tables works for me, so I guess that is the way to go for me. Still think it is a bug though

For the second issue I had, the solution was out here on the forum, but (re) post it here, just in case somebody is looking

The value in the ID field is a large number, and the text representation was in scientific notation. (Mix)Match is a string comparison function so the string I was matching did not match the scientific notation. However

text(num(<id-field>,(dec)) did the job. It converts scientific notation to 'normal'

Kind regards,