Data corruption? in Qlikview

[#1]

I am having a very strange data issue with Qlikview and any assistance with tracking down why this is occuring would be much appreciated.

I have been trying to track down a difference between the values in a Qlikview report and a similar report out of Solomon (Dynamics SL, our accounting package). I finially tracked it down to the following which to me makes no sense and has me somewhat worried.

I run the following query in SQL Server Query Editior on our SQL Box:

SELECT [RefNbr]

,[DocType]

,[ProjectID]

FROM [SLDB]. [dbo]. [ARDoc]

WHERE RefNbr LIKE '%21883%'

and get the following result:

21883. CM Z9

0021883ADJ DM Z9

021883 IN 00B100121

Then I run this query in the Qlikview script:

SQL SELECT

RefNbr,

DocType,

ProjectID

FROM SLDB.dbo.ARDOC

WHERE RefNbr LIKE '%21883%'

;

and get this result:

21883.	CM	Z9
21883.	IN	00B100121
0021883ADJ	DM	Z9

If you notice the RefNum field is returning different results for the actual Invoice # (type = IN) specifically it is returning "21883." (with a period which is correct for the CM (Credit Memo)) rather than "021883"

The SQL query represents the correct result while the Qlikview one is wrong.

I don't have any other script other than the connection string in Qlikview and I am not using any qvds.

The results of the query are consistent but if I select on %21883.% or %021883% I get the expected result

Ultimately I want to select all invoices but when I do I still get bad data. This is just a small subset which demonstrates the problem.

Thanks for any help you can provide.

Nick Clift

[Miguel_Angel_Baeyens · Accepted solution]

Hello Nick,

Note that QlikView doesn't work with data types. Any value may be either numeric or text, unless some conversion using functions is done.

So the proper way to get the values you want to QlikView would look like

Data:
LOAD Text(RefNbr) AS RefNbr, 
     DocType, 
     ProjectID;
SQL SELECT RefNbr, DocType, ProjectID
FROM SLDB.dbo.ARDOC
WHERE RefNbr LIKE '%21883%';

Although the LOAD part is not mandatory, is very useful as it allows you to control what fields and how are they loaded into memory.

Hope that helps!

[#3]

Thank you very much Miguel that does solve my immediate problem.

I normally use load scripts to pull data but removed them in this case to reduce the # of possible issues.

I am posting a Qlikview demonstrating that you could change which value got selected for both the CM and IN by changing the ORDER you return them from the SQL statement.

As a follow up is Qlikview assuming what type of data the field is based upon the first value seen?

and why did it populate the other value with wrong data rather than some null?

and finally why didn't it effect the DM entry which was always a string? does it have anything to do with the special character?

We have a rather large Qlikview footprint and any information would be helpful for tracking down where I have to investigate if this is an issue

Thanks,

Nick Clift

[#4]

I got a server error trying to attach a file in Chrome, this post succeeded using IE.

[Miguel_Angel_Baeyens]

Hello Nick,

For what I've seen, and according to the following, my answer to your first question is yes or likely. I don't remember exactly where, but there were some posts in the forum about this subject, that the order of the fields loaded influences the rest of the values.

For the rest of the questions, since QlikView only understands numbers or literals, and since the values are not null, it pulls the raw data according to the first field. That's why you will need to convert should the field needs it. The third value cannot be a number, since it has alphanumeric characters, so it's dealt as "string".

I don't have your datasource but it seems it behaves likewise using inline data

Table:
LOAD * INLINE [
RefNbr, DocType, ProjectID
21883., CM, Z90
21883, IN, 00B100121
0021883ADJ, DM, Z9
]; 
Table2:
LOAD *, 
     Text(RefNbr) AS RefNbrText
RESIDENT Table
ORDER BY ProjectID DESC; 
DROP TABLE Table;

What leads me to think this way is that in one case (your ASC) the ending dot in the CM value may be both a decimal or a period, and QlikView leaves the symbol as such, understanding the value as numeric, but in if you move the second line to fhe first place (your DESC), it understands that the dot is a decimal separator, so it's numeric, and correspondingly removed ( ? ). It might be a bug on displaying the data.

Actually, you will see that in both cases the original RefNbr is dealt as number, by default is aligned to right, and in with the Text() is on left.

Moreover, the code above will load differently if you set

SET DecimalSep=',';

(my system default, Spanish language, "21883." will be a string), than if you set a period as decimal separator instead ("21883." will be a number).

Anyway I'll try to find the post I'm referring as soon as I find it.

[#6]

Qlikview interpting the "." as a decimal seperator makes most of it make sense.

The only thing I question is based upon what I am seeing, if the data does not mesh with what Qlikview is expecting it is somehow grabing the nearest value in its pointer/hash based system. This is the only way I can explain the trasposition of "021883" and "21883." which are related records but in the database are not squential. The transactions are actually a few months apart and this particular swap is present if I pull the full dataset. (There are other swaps)

Regardless of the error the Text() function solves the problem and the "." explains why I havn't run into it before (I've been using Qlikview for about 2 years)

Thanks for the help,

Nick

[Miguel_Angel_Baeyens]

For what is worth, here is what I've found in the Reference Manual v9 SR6 (pp. 815, Book II, Par. 22.1):

22.1 Data Representation inside QlikViewIn order to understand data interpretation and number formatting in QlikView, it isnecessary to know how data is stored internally by the program. All of the dataloaded into QlikView is available in two representations, as a string and as a number.1 The string representation is always available and is what is shown inthe list boxes and the other sheet objects. Formatting of data in listboxes (number format) only affects the string representation.2 The number representation is only available when the data can beinterpreted as a valid number. The number representation is used forall numeric calculations and for numeric sorting.If several data items read into one field have the same number representation, theywill all be treated as the same value and will all share the first string representationencountered. Example: The numbers 1.0, 1 and 1.000 read in that order will all havethe number representation 1 and the initial string representation 1.0.

Regards.