topic Text field formatted as a number in QlikView

Text field formatted as a number

Fri, 19 Jun 2009 19:00:00 GMT

Hi all,

Got an odd one here (using 8.20).

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.

Why is Qlikview doing this?

Regards,

Gordon

Text field formatted as a number

Fri, 19 Jun 2009 19:01:01 GMT

and here is the xls....

Text field formatted as a number

johnw — Sat, 20 Jun 2009 03:39:25 GMT

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.

Text field formatted as a number

Anonymous — Sat, 20 Jun 2009 03:46:43 GMT

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.

Text field formatted as a number

johnw — Sat, 20 Jun 2009 04:07:57 GMT

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.

Text field formatted as a number

rwunderlich — Sat, 20 Jun 2009 06:25:43 GMT


gordon.savage wrote:formatting it as number

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.

That's my $0.02 theory for Friday afternoon.

-Rob

Text field formatted as a number

Mon, 22 Jun 2009 15:30:39 GMT

Thanks for the replies.

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.

Regards,

Gordon