topic Re: Qlikview alters zero-padded values in QlikView

Qlikview alters zero-padded values

Mon, 09 Nov 2015 16:18:40 GMT

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!

Re: Qlikview alters zero-padded values

maxgro — Mon, 09 Nov 2015 16:38:22 GMT

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;

Re: Qlikview alters zero-padded values

Tue, 10 Nov 2015 14:06:17 GMT

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

Re: Qlikview alters zero-padded values

maxgro — Tue, 10 Nov 2015 18:29:52 GMT

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.