topic Re: Text loading as numbers in QlikView

Text loading as numbers

Mon, 03 Mar 2014 13:19:43 GMT

Hi,

I have a table in access database with column cameraname and this column contains character(text) adat. When I am loading this table the character data is loading as numbers. can anyone let me know please what is this strange behaviour.

Thanks.

Re: Text loading as numbers

maxgro — Mon, 03 Mar 2014 13:22:26 GMT

try with

text(field)

Re: Text loading as numbers

alexandros17 — Mon, 03 Mar 2014 13:22:55 GMT

If you load a string made of digits it is recognized as number, write text(myField) to force as text

Re: Text loading as numbers

MK_QSL — Mon, 03 Mar 2014 13:26:31 GMT

Text(FieldName) as FieldName

Re: Text loading as numbers

Mon, 03 Mar 2014 13:27:18 GMT

I tried text(myfield) but still showing numbers. Any solution please

Re: Text loading as numbers

dirk_konings — Mon, 03 Mar 2014 13:30:28 GMT

try to add a space after the value.

text(myfield) & ' ' as NewField

Re: Text loading as numbers

alexandros17 — Mon, 03 Mar 2014 13:31:06 GMT

Initially week is a number, executing this script it become a string (you see it in the left side of the column)

load year, text(week) as week;

select year, week from period;

Re: Text loading as numbers

MK_QSL — Mon, 03 Mar 2014 13:33:45 GMT

can you upload your sample file please?

Re: Text loading as numbers

Mon, 03 Mar 2014 13:35:49 GMT

Like the same the numbers are showing on left of column. But I need names instaed of numbers, because in the database there are names. Any idea please.

Re: Text loading as numbers

Mon, 03 Mar 2014 13:36:09 GMT

Thanks. Still showing same numbers.

Re: Text loading as numbers

dirk_konings — Mon, 03 Mar 2014 13:38:32 GMT

Could it be a DUAL field in Db ?

You show a text but in database you remember the corresponding number?

Re: Text loading as numbers

Mon, 03 Mar 2014 13:46:03 GMT

no there is only column with column name as 'cameranames'. In the database the column contains names of the cameras but when I am loading it is showing digits(numbers) instead of names. Any solution?

Re: Text loading as numbers

dirk_konings — Mon, 03 Mar 2014 13:48:40 GMT

Please add some printscreens of your table in Access and also the field definitions in Access.

Re: Text loading as numbers

Peter_Cammaert — Mon, 03 Mar 2014 13:52:25 GMT

In a listbox called 'cameranames', numbers will appear at the start. Are you sure there aren't any real names at the end of the list? It could be that the database field contains not only real cameranames, but also some (historical) numbers.

Re: Text loading as numbers

Mon, 03 Mar 2014 14:17:05 GMT

I am sure there are no numbers in database table. Why it is showing numbers?

EDIT: moreover when I copied the table column to excel and when reloading the data from excel I can able to see cameranames. But when I amloading directly from database it is showing numbers. Is there any reason for this behaviour?

Re: Text loading as numbers

Bill_Britt — Mon, 03 Mar 2014 14:19:29 GMT

Check this out in 11.20 SR 5 release notes.

Bug: 65030 Mixed Values with values like 1E4 are mismatched

When loading text fields QlikView has always tried to interpret numeric values and load them as numeric, not text. Due to two different bugs the behavior of QlikView interpretation of exponential numbers, nnnEn or nnnDn, has changed between releases. The safest way to make sure you get the interpretation you like has always been to cast the specific fields to the right type in the script.

For customers with many old scripts affected by the behavior change, it has been decided to implement a solution which allows a small degree of control of how QlikView will interpret text fields at script level. A script variable 'ExponentNumberNotation' has been introduced. It can be assigned:

0 meaning neither 1.23E6 nor 1.23D6 is interpreted as an exponential number

1 meaning 1.23E6 but not 1.23D6 is interpreted as an exponential number

2 meaning both 1.23E6 and 1.23D6 are interpreted as an exponential number

If not assigned a value in the script, 1 will be the default value.

Re: Text loading as numbers

Mon, 03 Mar 2014 14:24:03 GMT

Thanks. How to cast this?

Re: Text loading as numbers

Peter_Cammaert — Mon, 03 Mar 2014 14:51:06 GMT

SET ExponentNumberNotation = 0;

at the beginning of your script.

Re: Text loading as numbers

Mon, 03 Mar 2014 15:03:15 GMT

I used this . Still showing same numbers. Please can sort this as because of this I stucked in middle to produce the complete chart.

Re: Text loading as numbers

Peter_Cammaert — Mon, 03 Mar 2014 15:30:06 GMT

Please post your document (without section access).

Otherwise we can only continue making wild guesses, and you'll be stuck in the middle for I-don't-know-how-much-longer. Thanks.