Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.
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);
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