topic Load in QlikView

Load

Thu, 17 Oct 2013 21:40:05 GMT

Hello everybody, I need your help please. I have a worksheet called Aplicaciones_Backlog_priorizadas xls.xlsx and i want to load the data in different qvd like CAMPOS_BACKLOG201301.qvd, CAMPOS_BACKLOG201302.qvd, etc.

I have the notepad with this variables:

// carga diaria, mes actual y anterior

LET vANIO=num( year( AddMonths( Today(),-1)));

LET vMES =num( Month( AddMonths( Today(),-1)));

LET vMESMAX=num( Month( Today()));

LET vANIOMAX=num( year(Today()));

Then in my qlikview file I put the following:

$(Include=..\1_Recursos\VARIABLES_CARGA.txt);

//Comentar para hacer la recarga por periodo

LET vFIN= vANIOMAX & if(num(vMESMAX)>9,vMESMAX,'0'&vMESMAX);

LET vINICIO = vANIO& if(num(vMES)>9,vMES,'0'& vMES) ;

do while vINICIO<=$(vFIN)

LET vANIO= num(mid($(vINICIO),1,4));

LET vMES = num(mid($(vINICIO),5,2));

CAMPOS_BACKLOG:

NoConcatenate

LOAD Id_Tarea,

Descripción,

Prioridad,

[Fecha Registro],

[Fecha Necesidad],

Origen,

Usuario,

Estado,

[Fecha Priorización],

Observaciones,

Estimación,

Responsable,

[Número iteración],

[Módulo del sistema],

[Fecha Fin],

[En Tablero],

ANO,

MES

FROM

[..\..\1_Recursos\DashBoard_TI\Aplicaciones_Backlog_priorizadas xls.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Backlog_Priorizadas);

CAMPOS_BACKLOG2:

NoConcatenate

LOAD *

Resident CAMPOS_BACKLOG

WHERE ANO=$(vANIO) AND MES=$(vMES);

DROP TABLE CAMPOS_BACKLOG;

Store CAMPOS_BACKLOG INTO D:\Qlikview\desarrollo\0_Scripts\Dasboad_TI\QVD_Dashboad_TI\CAMPOS_BACKLOG$(vANIO)$(vMES).qvd;

DROP TABLE CAMPOS_BACKLOG2;

let vINICIO= num(year(AddMonths(MakeDate(num($(vANIO)),num($(vMES)),1),1))) & if(num(Month(AddMonths(MakeDate(num($(vANIO)),num($(vMES)),1),1)))>9,

num(Month(AddMonths(MakeDate(num($(vANIO)),num($(vMES)),1),1))),'0'&num(Month(AddMonths(MakeDate(num($(vANIO)),num($(vMES)),1),1))));

loop

and when i do Qlikc in Load bottom, the program show me the following message (look the picture)

. Can you help me please?? Thanks a lot!

Re: Load

Peter_Cammaert — Thu, 17 Oct 2013 21:59:34 GMT

Apparently, the value of $(vMES) in clause WHERE ANO=$(vANIO) AND MES=$(vMES); evaluates to nothing, resulting in:

WHERE ANO=0 AND MES=;

which is an invalid construct.

Check the line

LET vMES = num(mid($(vINICIO),5,2));

for a valid result. If vINICIO contains nothing, then the mid() and num() function calls will return ... nothing.

Good luck,

Peter

Re: Load

swuehl — Thu, 17 Oct 2013 22:05:44 GMT

Both variables used in the where clause don't seem to hold correct values:

WHERE ANO=$(vANIO) AND MES=$(vMES);

vANIO seems to be zero and vMES NULL.

I would suggest that you use the debug dialog (open script editor, then enter debug dialog) and step through your scipt statement by statement.

Escecially take care that your included script is opened correctly (all LET statements need to be executed).

You should see how the variables are evaluated when stepping through, so you should be able to see the statement where the evaluation fails.

Re: Load

CELAMBARASAN — Thu, 17 Oct 2013 22:11:19 GMT

Problem seems to be in your variables.

May be the belo one

LET vANIO= num(Num#(mid($(vINICIO),1,4), '####'));

LET vMES = num(Num#(mid($(vINICIO),5,2), '0#'));

or check the include path is valid

$(Include=..\1_Recursos\VARIABLES_CARGA.txt);

Re: Load

Peter_Cammaert — Thu, 17 Oct 2013 22:12:23 GMT

Most probably, the cause of this weird behavior is be even simpeler.

Make sure that your $(Include) statement actually does load the file with LET statements. If the .txt file can not be found, $(Include) will fail without some much as a warning message. To avoid being in the dark, put a TRACE statement in your include file, right after the last LET ANIOMAX... statement. Somethng like:

TRACE >>> Configuration file correctly included! <<<;

If all goes well, this message should appear in the message dialog when reloading the application. If not, you'll know what to do...

Peter