topic Re: Not working Where condition by using variables in QlikView

Not working Where condition by using variables

Wed, 03 Aug 2011 09:03:07 GMT

Hi,

In my QVW file, taken two variables with "DateTime" data.

MM-DD-YYYY hh:mm:ss

vStartDt = '07-07-2007 00:00:01'

vEndDt = '07-09-2009 00:00:15'

Now in editscript writing where condition where CaptureDateTime > $(vStartDt) and CaptureDateTime < $(vEndDt),

but it is not fetching any data.

While loading the script it is not fetching any data.

Is there any mistake in providing a value to variables or any mistake in where condition.

Can anyone suggest on this.

Thanks in advance

Regards

Venkat

Not working Where condition by using variables

SunilChauhan — Wed, 03 Aug 2011 09:04:28 GMT

write variable in single quote

CaptureDateTime < '$(vEndDt)'

hope this may help u

Not working Where condition by using variables

Wed, 03 Aug 2011 09:07:24 GMT

Write like this:-

where CaptureDateTime > '$(vStartDt)' and CaptureDateTime < '$(vEndDt)'

Note:- Write variable within single quote(' ').

Re: Not working Where condition by using variables

Wed, 03 Aug 2011 09:40:53 GMT

Hi kumar,

I tried what you said, but it is generating an error i.e; Garabage After Statement

vStartDt = '07-07-2007 00:00:01'

vEndDt = '07-09-2009 00:00:15'

where CaptureDateTime > '$(vStartDt)' and CaptureDateTime < '$(vEndDt)';

Regards

Venkat

Re: Not working Where condition by using variables

Miguel_Angel_Baeyens — Wed, 03 Aug 2011 10:06:46 GMT

Hello Venkat,

Are you loading your variables with LET or with SET ? Are you quoting them when assigning to the variable? Has CaptureDateTime the same exact format?

Check the following working example:

SET vStartDt = '07/07/2007 00:00:01';
SET vEndDt = '07/09/2009 00:00:15';
LOAD * INLINE [
ID, CaptureDateTime
A, 07/07/2007 00:00:01
B, 07/09/2009 00:00:15
C, 07/09/2009 00:00:16
]
WHERE CaptureDateTime >= '$(vStartDt)' AND CaptureDateTime <= '$(vEndDt)';

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Not working Where condition by using variables

Wed, 03 Aug 2011 10:19:25 GMT

Hi Miguel, Thanks for reply

I am not assigning the variable inside the EditScript.

I assigned variables outside the EditScript i.e; In DocumentProperties --> Variables i.e; the variables are getting from another QVW file.

vStartDt = '07-07-2007 00:00:01'

vEndDt = '07-09-2009 00:00:15'

Regards

Venkat

Re: Not working Where condition by using variables

Miguel_Angel_Baeyens — Wed, 03 Aug 2011 10:34:25 GMT

Hi Venkat,

Then that's the problem. If you are quoting the values in the Settings menu, Variable Overview, you don't need to quote them again in the WHERE statement, otherwise the "garbage after statement" error shows up. Remove the quotes in the values using the Variable Overview menu or in the WHERE and you get it.

WHERE CaptureDateTime >= $(vStartDt) AND CaptureDateTime <= $(vEndDt);

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Not working Where condition by using variables

Wed, 03 Aug 2011 11:08:38 GMT

Hi Miguel, sorry for late reply.

I tried that also but not getting the data.

I attached the sample file and qvd. Pls check this.

Regards

Venkat

Re: Not working Where condition by using variables

Miguel_Angel_Baeyens — Wed, 03 Aug 2011 11:18:57 GMT

Venkat,

Try the following code:

Test:
LOAD CaptureDateTime
FROM
SampleDate.qvd
(qvd) WHERE Date(CaptureDateTime) > '$(vStDt)' AND Date(CaptureDateTime) < '$(vEnDt)';

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica