Some characters magically appear in my data

[#1]

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.

[jonathandienst]

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

[Anonymous · Accepted solution]

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

16

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.

[Anonymous]

That's what I get for typing a long answer