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!
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
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
(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)
Thanks for the help,
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.