ISSUE MS ACCESS - data loss

[#1]

I am facing a problem that I really struggle to understand.

I'll try to explain.

I work for loading data stored in an MS-ACCESS database. The file comes with me mdb extension.

A table is devoted to product classifications: a text field [codigo] for an alphanumeric code (therefore unique key), a text field [description] (the name of the category) and 4 boolean fields (unexploited because all values 0).

I load via a SQL command the fields [codigo] and [Description].

The result everything looks perfectly normal. But by counting the number of distinct value for the [codigo] I realize that I have a data loss.

Very strangely certain field values ​​[codigo] disappear. In contrast, the values ​​of the field [description] corresponding busy but are associated a [codigo] wrong. In this case the previous key when viewing the ordered [codigo] in access table.

I checked the ACCESS table and it doesn't seems to have strange invisible characteres that may provoc an "understandable" issue…

I tried to load only the [codigo] field and the loss value doesn't appear. But if I try a load with WHERE codigo = 'myLossValue' I get it....

Here is the screen shot of the access table

the loss value is 231E00 and in QV the table comes with 'Hormonales' associated with the codigo 231D00

If you have any beginning of explaination for this issue I would really be beholden!

Thanks for your time

Jerome

[konrad_mattheis]

Hi,

for me the most important thing is, that you see the 231D00 is right allignt in the listbox. So this is number and I don't know why but qlikview recognize 231D00 and 231E00 as the same number.

Please Load the Data with LOAD 'ID'&codigo ... -> SELECT * FROM ...

bye

Konrad

[s_uhlig]

Hi Jerome,

the root cause is described here: http://community.qlik.com/thread/99627

Regards

Sven

[#4]

Hi

thank you for both of you.

reading the thread mentioned by Sven it appears that there is an issue du to an exponential interpretation of alphanumerique string finishing with 'Dnn' or 'Enn' where 'n' is number.

It seems to have been implemented a function ExponentNumberNotation to controle this issue. I'll try to understand how to use it. If I succed I'll let you know how

Regards

[#5]

Simple to use ExponentNumberNotation is not a funcion but a global variable.

it has to be set before the Load statement.

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.

---------------- editing --------------------

I tried this global variable and nothing changes...

[rbecher · Accepted solution]

Just load it as text:

LOAD text(codigo) as codigo, ... ;

SQL SELECT ...

[#7]

Thanks Ralf

It works but it's not very clear how. However I copy right here the exact script which works for the further users be abble to apply more easely you're solution (that I had already read but not tried because not understund)

---- the script which works ----

LOAD text(codigo) as codigo, Descripcion;

T_classification_temp:

NoConcatenate

SQL SELECT

codigo,

Descripcion

FROM Clasificacion;