Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Qlikview alters zero-padded values

Dear all,

I've a problem loading in Qlikview particular codes from a dBase file.

The script I'm using is very simple:

ODBC CONNECT32 TO [dBase Files];

SQL SELECT *

FROM .\File.dbf;

Some fields in the source DBF file use a particolar coding system in which, for example, '52', '052', '0052' are three completely different codes. Qlikview seems to fail to load correctly these values, randomly adding or removing inital '0' digit, for example:

code '0042' becomes '042' and  code '539' becomes '0539'.

I've tried to change the ODBC driver (MS Access dBase Driver v. 15) with an OLEDB driver (Visuale FoxPro) but the problem still remains; using the same ODBC driver in excel the values are loaded correctly so it isn't a bug of the driver and the original dfb file isn't damaged.

How can I force Qlikview to load these values without trying to alter/modify/interpret anything?

Thanks!

3 Replies
MVP
MVP

Re: Qlikview alters zero-padded values

try adding text in the preceding load to these fields

load

     text(field) as field,

     text(field2) as field2,

     ..... other fields

      ...

     ;

SQL SELECT *

FROM .\File.dbf;

Not applicable

Re: Qlikview alters zero-padded values

Dear maxgro,

thank you very much: using preceding load QV the values are correctly loaded. At this point, the only problem is that the correct values are renamed, in fact the commands:


load

     text(field) as field,

...

don't work because 'field' is already present in the table; instead I've used:


load

     text(field) as field_NEW,

...

I think I'll have to load again the table to bring back 'field_NEW' to the original name and drop out the wrong values:

load

     field_NEW as field,

     ....

Resident ....;

Carlo

MVP
MVP

Re: Qlikview alters zero-padded values

the preceding load doesn't change the names if you rename the field with the same name of the SQL SELECT ....

without preceding load: you have 2 fields with names field1 and field2

SQL SELECT

     field1,

     field2

FROM TABLE;

with preceding load: you have the same 2 fields with the same names field1 and field2

load

     text(field1) as field1,

     text(field2) as field2;

SQL SELECT

     field1,

     field2

FROM TABLE;

What I mean is that if you have a problem with field1 already present in the table, this should happen with or without preceding load.

Community Browser