How to load xls file into variable

Wed, 14 Sep 2011 14:50:35 GMT

HI,

I read a lot of case on this forum to load data into variable.

But my case is not working. I think it is very simple but ... -)

So. I have a XLS file with 3 colums :

Annee	Mois	DateSel
2011	6	20110615

I load this file like this :


SET vAnnee = null();
param : 
LOAD Annee as Annee_sel,
     Mois as Mois_sel,
     DateSel as Date_sel
         
FROM
H:\Cofidis\JVE\param_EASI.xls
(ooxml, embedded labels, table is Feuil1);
;;

Then , do this

LET vAnnee = num(PEEK('Annee_Sel', 0, 'param'));

And

COMPTA :

LOAD

MNGSOCCD,

MNGPOSCD,

DCEXPRCD as PERIODE,

DOCPERCD as MOIS,

DOCEXECD as ANNEE,

JRNCODCD as JOURNAL,

DOCNUMCD as PIECE,

DOCLINCD as LIGNE,

DOCDATCD,

JRNTYPCD as JOURNAL_TYPE,

GENACCCD as COMPTE,

CRLCCUCD - DELCCUCD as MONTANT,

ANAACCCD,

FLRREFCD,

COMNUMCD,

SALNBRCD,

PURNBRCD,

MVTDSCCD as DESCRIPTION,

CREUSRCD,

CREDATCD,

UPTUSRCD,

UPTDATCD ,

;

SQL SELECT MNGSOCCD,

MNGPOSCD,

case when MNGPOSCD = 1 then 'Gestion' else 'Compta' end as BU,

DCEXPRCD,

DOCPERCD,

DOCEXECD,

JRNCODCD,

DOCNUMCD,

DOCLINCD,

DOCDATCD,

JRNTYPCD,

GENACCCD,

DELCCUCD ,

CRLCCUCD,

ANAACCCD,

FLRREFCD,

COMNUMCD,

SALNBRCD,

PURNBRCD,

MVTDSCCD,

CREUSRCD,

CREDATCD,

UPTUSRCD,

UPTDATCD

from

CMOUVEDT

where GENACCCD between 600000000 and 799999999 And MNGSOCCD = 1 AND MNGPOSCD in (1,2)

and DOCEXECD = $(vAnnee) and DOCPERCD = 06

;

on load i have this message

SQL##f - SqlState: 37000, ErrorCode: 156, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntaxe incorrecte vers le mot clé 'and'.

SQL SELECT MNGSOCCD,

MNGPOSCD,

case when MNGPOSCD = 1 then 'Gestion' else 'Compta' end as BU,

DCEXPRCD,

DOCPERCD,

DOCEXECD,

JRNCODCD,

DOCNUMCD,

DOCLINCD,

DOCDATCD,

JRNTYPCD,

GENACCCD,

DELCCUCD ,

CRLCCUCD,

ANAACCCD,

FLRREFCD,

COMNUMCD,

SALNBRCD,

PURNBRCD,

MVTDSCCD,

CREUSRCD,

CREDATCD,

UPTUSRCD,

UPTDATCD

from

CMOUVEDT

where GENACCCD between 600000000 and 799999999 And MNGSOCCD = 1 AND MNGPOSCD in (1,2)

and DOCEXECD = and DOCPERCD = 06

The $(vannee) is NULL.

What's the problem ?

Thx.

How to load xls file into variable

Wed, 14 Sep 2011 14:56:15 GMT

Hi!

Field names are case sensitive. The field you're trying to lookup is 'Annee_sel', but you're doing the Peek on 'Annee_Sel'.

That will cause the Peek to return Null.

Brian

How to load xls file into variable

IAMDV — Wed, 14 Sep 2011 14:57:34 GMT

Your field name - Annee_sel is in wrong case.

That should work.

Cheers - DV

How to load xls file into variable

Wed, 14 Sep 2011 14:59:51 GMT

Work great.

Thx.

How to load xls file into variable

Wed, 14 Sep 2011 15:09:56 GMT

Hello,

in addition to the posts above.

For testing (and shorten waiting-time) you can use the trace - command like this:

TRACE vAnnee: $(vAnnee);

Regards, Roland

topic How to load xls file into variable in QlikView

How to load xls file into variable

How to load xls file into variable

How to load xls file into variable

How to load xls file into variable

How to load xls file into variable