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:
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:
WHERE RefNbr LIKE '%21883%'
and get this result:
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.
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
LOAD * INLINE [
RefNbr, DocType, ProjectID
21883., CM, Z90
21883, IN, 00B100121
0021883ADJ, DM, Z9
Text(RefNbr) AS RefNbrText
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
(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.
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)
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.