Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

Not working Where condition by using variables

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

8 Replies
Highlighted
SunilChauhan
Esteemed Contributor

Not working Where condition by using variables

write variable in single quote

CaptureDateTime < '$(vEndDt)'

hope this may help u

Highlighted
Not applicable

Not working Where condition by using variables

Write like this:-

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

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

Highlighted
Not applicable

Re: Not working Where condition by using variables

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

Highlighted

Re: Not working Where condition by using variables

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

Highlighted
Not applicable

Re: Not working Where condition by using variables

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

Highlighted

Re: Not working Where condition by using variables

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

Highlighted
Not applicable

Re: Not working Where condition by using variables

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

Highlighted

Re: Not working Where condition by using variables

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