topic Re: Some characters magically appear in my data in QlikView

Some characters magically appear in my data

Wed, 05 Dec 2012 09:48:49 GMT

Hi all,

I have a problem with my qlikview. Some characters are added to the data when loading data using OLEDB. (Connect to sql server 2008)

For example I have a field called INVNO in TableA and it has value like '0016' and '1004244'. (They have data type of char(50).

When i Load them into qlikview,

'0016' become '000016' (2 zeros were added)

and '1004244' become '1004244.' (there's a dot at the end)

when i put 'where condition' in my sql select statement in qlikview :

WHERE INVNO = '0016' OR INVO = '1004244';

the data appeared as is without additional character.

I also have that field in another table (Table B) as foreign key and it loads perfectly.

Has anyone else ever faced the same problem?

Thanks for your help.

Re: Some characters magically appear in my data

jonathandienst — Wed, 05 Dec 2012 09:59:43 GMT

Reynold

QV tries to format the data according to the values in the first few records it reads. In your case, you want to keep the values as text - use the following

LOAD Text(INVNO) As INVNO,

....

Now the values will be the same as in the source database (0016 and 1004244)

Hope that helps

Jonathan

Re: Some characters magically appear in my data

Anonymous — Wed, 05 Dec 2012 10:01:47 GMT

Hi Reynald,

This is what is happening.

When QlikView loads data values that is has already loaded before, it merely creates a reference to this value, so each unique value is stored once.

In the case of 0016 and 000016, QlikView will interpret them as the same value (16), and it will use the 1st encountered version to represent all of them.

So, if you load the following field:

000016

0016

You will have three values (16) that are represented as 000016, since it's the first instance of 16. If you want to break this auto-association for your field you can use Text() around the fieldname and QlikView will respect the characters in the field instead of applying a numerical interpretation on the field.

Re: Some characters magically appear in my data

Anonymous — Wed, 05 Dec 2012 10:02:27 GMT

That's what I get for typing a long answer