I have a column in an XLS (cells formatted as 'General') which contain text (nu numbers). When I load this into the document, Qlikview insists on formatting it as number (even changing it through document properties > number > mixed has no effect. In a list box, the mixed attribute is greyed out.
Either there's a difference in 8.5, or I'm not understanding the question. Looking at your Excel file, I see a list of groups, all of which are either 'Oil' or 'Other'. Looking at the Qlikview file, I see a list box with both 'Oil' and 'Other'. They are left justified, hinting that they are text. Nothing is grayed out. Showing the frequency indicates that many different rows were read in. Everything looks exactly like I would expect.
That said, if you want to force QlikView to load a field as text, just load text(Field) as Field.
John, I don't know if it is a real problem, but it looks odd. Try this: Open Document Properties, tab Number. Select field Group - see that it is "Number" Change to "Mixed", and Apply. Select the field again - it is "Number", not "Mixed". As a result, in the List Box properties, tab Number, "Mixed" option is not available. I tried in 8.50 and 9.
You're right; that is strange. However, I was able to fix it by changing the column to text instead of general in the Excel, setting the field format to mixed and survive reload, and reloading. And the text() function works too, though again, you have to fix the properties of the column too. So I don't know why it appears to be misbehaving, and whether or not it's actually a problem, but it's easy enough to fix. Definitely a curiosity, though.
It's kind of an interesting dialog behavior, but I'd suggest it's a non-issue. "General" is an Excel numeric type, so under the rules of QV the Numeric type is assigned. According to the Ref Guide, for fields that are typed from their source:
"QlikView will remember the original number format of the field even if the number format is changed in the number format dialogs of the application."
Strings do not get formatted, so anything you could do on the Number tab would have no effect on the string values in the example.
Here is my theory as to why you can't change to mixed in the dialog.
1. Mixed data is always stored internally as Strings. Very wasteful for storage but maybe the best QV can do if no type information is supplied at load time.
2. If a datasource is typed as numeric (like your example), QV attempts to store each and every value internally as a number. Better utilization of storage and better expression performance. Those values that are not really numbers get stored as Strings.
3. Mixed data is displayed as-is from storage. It's already in String form so it's just copied from storage to display. Internally stored numerics must have some formatting declared. So for your example, you have some internal numerics, therefore you are forced to specify a format.
It is very odd behaviour - especially given that the cells are formatted as general in the xls. The data is rendering quite correctly (aligned left) so QV identifies the data as text therefore one would expect to see the formatting as 'mixed'.
I quite often find anomalies with QV and XLS (it would be rude of me to point at the suspect). I had one the other day where I used 'cross table' on columns that had number formatting and sometimes (randomly) QV saw the data as the formatted values and others the underlying actual values.
