Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
So I have this value in a excel file that I save in a variable on qlikview script.
I need to use the value of this variable on a sql query but I am having problems.
What happens is that instead of having the value of the variable I have the expression...
For example:
//The value (DataTesteSCI) was already loaded from the excel file and here I set the value of the variable.
SET maxDateFicheiroSCI = max(DataTesteSCI);
//Here I save the value of the previous value into another one
SET strPreviousDate = $(maxDateFicheiroSCI);
//on my sql query I have this clause:
RESULT_TEST.TESTTIME between to_date($(strPreviousDate),'DD-MM-YYYY%') and to_date($(strCurrentDate),'DD-MM-YYYY%'); |
And with this clause I have an error where the $(strPreviousDate) value is replaved by the original expression max(DataTesteSCI).
I have no idea why this happens I have tried a lot of things to try to fix it.
Any ideas?
Best regards!
LET maxDateFicheiroSCI = max(DataTesteSCI); doesn't do anything. You need to use a load statement to calculate the max date:
Table1:
LOAD KNRSCI,
DataTesteSCI,
HoraTesteSCI,
DescricaoModuloSCI,
CodigoFalhaSCI,
DescricaoFalhasSCI,
EstatisticoFalhasSCI
FROM
(ooxml, embedded labels, table is SCI_export);
Temp:
LOAD max(DataTesteSCI) as maxDate RESIDENT Table1;
LET maxDateFicheiroSCI = date(peek('maxDate'),'DD-MM-YYYY');
DROP TABLE Temp;
And in the sql statement you will need to put single quotes around the variable:
and RESULT_TEST.TESTTIME between to_date('$(vTeste)','DD-MM-YYYY%') and to_date($(strCurrentDate),'DD-MM-YYYY%');
I think you need to read this blog post: The Magic of Variables
Hello Gysbert,
Thank you for the post about variables, was very helpful, but I'm afraid I still have the same problem.
I will show you my code and try to explain again from there.
So here it is:
LOAD KNRSCI,
DataTesteSCI,
HoraTesteSCI,
DescricaoModuloSCI,
CodigoFalhaSCI,
DescricaoFalhasSCI,
EstatisticoFalhasSCI
FROM
(ooxml, embedded labels, table is SCI_export);
LET maxDateFicheiroSCI = max(DataTesteSCI);
LET vTeste = $(maxDateFicheiroSCI);
LET strCurrentDate = chr(39) & Date(now()) & chr(39);
OLEDB CONNECT TO [DATA BASE CONNECTION STRING];
SELECT DISTINCT RESULT_TEST.PID as "KNRSCI",
to_char(RESULT_TEST.TESTTIME,'DD-MM-YYYY') as "DataTesteSCI",
to_char(RESULT_TEST.RECORDTIME,'HH24:MI:SS') as "HoraTesteSCI",
UTL_RAW.CAST_TO_VARCHAR2(RESULT_RELEASEPBL.TEXT) as "DescricaoModuloSCI",
RESULT_ERROR.LOCATION as "CodigoFalhaSCI",
UTL_RAW.CAST_TO_VARCHAR2(RESULT_ERROR.TEXT) as "DescricaoFalhasSCI",
RESULT_TESTSTEP.STATNR as "EstatisticoFalhasSCI"
FROM RESULT_TEST, RESULT_IDENT, RESULT_TESTPBL, RESULT_TESTSTEP, RESULT_RELEASEPBL, RESULT_RELEASE, RESULT_TESTSTEPERROR, RESULT_ERROR
WHERE RESULT_TEST.PID=RESULT_IDENT.PID
and RESULT_TEST.TESTRESID=RESULT_TESTPBL.TESTRESID
and RESULT_TEST.TESTRESID=RESULT_TESTSTEP.TESTRESID
and RESULT_TESTSTEP.TESTRESID=RESULT_TESTPBL.TESTRESID
and RESULT_TESTSTEP.PBLNR=RESULT_TESTPBL.PBLNR
and RESULT_TEST.RELEASEID=RESULT_RELEASE.RELEASEID
and RESULT_RELEASE.RELEASEID=RESULT_RELEASEPBL.RELEASEID
and RESULT_ERROR.ERRORID=RESULT_TESTSTEPERROR.ERRORID
and RESULT_TESTSTEPERROR.TESTRESID=RESULT_TESTSTEP.TESTRESID
and RESULT_TESTSTEPERROR.PBLNR=RESULT_TESTSTEP.PBLNR
and RESULT_TESTSTEPERROR.STATNR=RESULT_TESTSTEP.STATNR
and RESULT_TEST.PLACE like ('ET%')
and RESULT_IDENT.TYPE like ('13%')
and RESULT_TESTSTEP.RESULT <> '0'
and RESULT_TESTSTEP.PBLNR=RESULT_RELEASEPBL.PBLNR
and RESULT_TEST.TESTTIME between to_date($(vTeste),'DD-MM-YYYY%') and to_date($(strCurrentDate),'DD-MM-YYYY%');
So, when I use the variable vTeste on the sql query i get the error on the attachment.
On the error the variable vTeste does not seem to have any value inside.
I think I have this made according to the post you showed me (The Magic of Variables), but it does not work and I don't know what to do...
Can you help?
Thank you!!
LET maxDateFicheiroSCI = max(DataTesteSCI); doesn't do anything. You need to use a load statement to calculate the max date:
Table1:
LOAD KNRSCI,
DataTesteSCI,
HoraTesteSCI,
DescricaoModuloSCI,
CodigoFalhaSCI,
DescricaoFalhasSCI,
EstatisticoFalhasSCI
FROM
(ooxml, embedded labels, table is SCI_export);
Temp:
LOAD max(DataTesteSCI) as maxDate RESIDENT Table1;
LET maxDateFicheiroSCI = date(peek('maxDate'),'DD-MM-YYYY');
DROP TABLE Temp;
And in the sql statement you will need to put single quotes around the variable:
and RESULT_TEST.TESTTIME between to_date('$(vTeste)','DD-MM-YYYY%') and to_date($(strCurrentDate),'DD-MM-YYYY%');
Worked like a charm
Thank you Gysbert!!!