topic Load field with ODBC Drivers in QlikView

Load field with ODBC Drivers

Mon, 09 Nov 2009 21:00:59 GMT

Hello,

When i load the field NOCLI Customer Number (Alphanuméric Value) from Db2. QV automaticaly Add Zeros Before.

Db2 : Field Value '000001' -> QV Field Value ='000001'

Db2 : Field Value '1bbbbb' -> QV Field Value ='000001' (Where b=blanck)

The 2 Db2 Field are différent but in QV there are equal = Problem

Is there any way that QV don't add the Zeros Before. (Force the fied to load in AlphaNuméric Value) ?

Thanks

Load field with ODBC Drivers

Mon, 09 Nov 2009 21:14:08 GMT

Did you check if you have this kind of options in your ODBC Driver settings ?

Load field with ODBC Drivers

GabrielAraya — Mon, 09 Nov 2009 21:24:37 GMT

Hi, Did you try with the Evaluate() function? Sometimes the ODBC return a numeric field with zeros.
Could you try:
Load
Evaluate(field) as field,
:
;

SQL Select ......;
Gabriel

Re. :Re: Load field with ODBC Drivers

Mon, 09 Nov 2009 21:24:50 GMT

When i look in the result of the odbc drivers. The result is good

One field with '1bbbbb' and one with '000001'. It seem becoming from QV

Re. :Re: Load field with ODBC Drivers

Mon, 09 Nov 2009 21:39:11 GMT

I try to use the function text() in the load statement and it seem to be good.

Thanks

Re. :Re: Load field with ODBC Drivers

Tue, 10 Nov 2009 16:53:21 GMT

Text() Fonction works well, but in our application we use a lot of Alphanuméric field with numeric value in it. (All key field)

And finaly this is a problem everywhere

Is it possible to disable this fonction for all the server or all the document in QV ?

Don't undersand why QV dont respect the import value and transform it.

Re. :Re: Load field with ODBC Drivers

Tue, 10 Nov 2009 17:00:10 GMT

When Qlikview stores information, it doesn't really force their type.

If all your alphanumeric fields contains numbers, Qlikview will handle them as numeric.

There is no way to force key fields to be taken as characters. Except by adding TEXT as mentionned earlier. But is it really an issue ? I mean if all keys are in the same shape, then you just have to add Text function before or add string : '' &" Fieldname as Fieldname for example LOAD '' & MyField as MyField, this should work also...

Rgds,

Re. :Re: Re. :Re: Load field with ODBC Drivers

Tue, 10 Nov 2009 17:27:21 GMT

Try to use this syntax. But i have always a script error

[ACLIENP1]:
LOAD
"&" NOCLI as NOCLI,
*;
SQL SELECT
// ETCLE,
// USCLE,
// DCLCS,
// DCLCA,
// DCLCM,
// DCLCJ,
// WSCLR,
// DCLMS,
// DCLMA,
// DCLMM,
// DCLMJ,
NOCLI,

NMCLI,

FROM $(vSysteme).$(vBibliothequeBASECOM).ACLIENP1;
STORE * FROM [ACLIENP1] INTO $(vAdherentRadical)\ACLIENP1.QVD;
DROP TABLE [ACLIENP1];

Re. :Re: Re. :Re: Load field with ODBC Drivers

Tue, 10 Nov 2009 17:33:31 GMT

I would do:

[ACLIENP1]:
LOAD
'' & NOCLI as NOCLI,
*;
SQL SELECT
// ETCLE,
// USCLE,
// DCLCS,
// DCLCA,
// DCLCM,
// DCLCJ,
// WSCLR,
// DCLMS,
// DCLMA,
// DCLMM,
// DCLMJ,
NOCLI,

NMCLI,

Re. :Re: Re. :Re: Load field with ODBC Drivers

Tue, 10 Nov 2009 17:34:15 GMT

I would rather do:

[ACLIENP1]:
LOAD
'' & NOCLI as "%NOCLI",
*;
SQL SELECT
// ETCLE,
// USCLE,
// DCLCS,
// DCLCA,
// DCLCM,
// DCLCJ,
// WSCLR,
// DCLMS,
// DCLMA,
// DCLMM,
// DCLMJ,
NOCLI,

NMCLI,

Re. :Re: Re. :Re: Re. :Re: Load field with ODBC Drivers

Tue, 10 Nov 2009 17:59:51 GMT

Ok this is a solution. I just have to always think to convert all the key field for good mapping

Domage there is no alternative

Thanks