Variable value not showing

[#1]

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!

[Gysbert_Wassenaar]

I think you need to read this blog post: The Magic of Variables

[#3]

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!!

[Gysbert_Wassenaar · Accepted solution]

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%');

[#5]

Worked like a charm

Thank you Gysbert!!!