topic Re: Re: Variable value not showing in QlikView

Variable value not showing

Wed, 03 Sep 2014 12:38:59 GMT

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!

Re: Variable value not showing

Gysbert_Wassenaar — Wed, 03 Sep 2014 13:21:01 GMT

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

Re: Re: Variable value not showing

Thu, 04 Sep 2014 13:05:12 GMT

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

Re: Re: Variable value not showing

Gysbert_Wassenaar — Thu, 04 Sep 2014 13:10:31 GMT

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

Re: Re: Variable value not showing

Thu, 04 Sep 2014 13:36:04 GMT

Worked like a charm

Thank you Gysbert!!!