Not working Where condition by using variables

[#1]

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

[SunilChauhan]

write variable in single quote

CaptureDateTime < '$(vEndDt)'

hope this may help u

[#3]

Write like this:-

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

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

[#4]

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

[Miguel_Angel_Baeyens]

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

[#6]

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

[Miguel_Angel_Baeyens]

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

[#8]

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

[Miguel_Angel_Baeyens]

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