Error on where clause with date criteria

[h_demarco]

People,

I am working around a "garbage after statement" error with following code:

//Concatenar com a o QVD já criado
IF not IsNull(QvdCreateTime('$(vDiretorio)QVDs\$(vFactTable).qvd')) then
Concatenate
LOAD * 
FROM $(vDiretorio)QVDs\$(vFactTable).qvd (qvd)
Where Timestamp(Timestamp#([DO DateTime])) < $(varMinDateTime)
End IF

Variable varMinDateTime is defined as (obviously it is defined before load):

Temp:  
Load  
               min([DO DateTime]) as minDate,  
               max([DO DateTime]) as maxDate
Resident $(vFactTable);    
Let varMinDateTime = Timestamp(Peek('minDate', 0, 'Temp'));  
Let varMaxDateTime = Timestamp(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;  

Error appear as follows:

Concatenate

LOAD *

FROM QVDs\Deliveries.qvd (qvd)

Where Timestamp(Timestamp#([DO DateTime])) < 02/02/2015 07:03:43

My guess is that error is in Where clause, because when I exclude it, script goes well.

What am I doing wrong? I just want to concatenate fields with creation date [DO DateTime] before variable value. I am not using SQL, but Qlikview syntax.

Field [DO DateTime] is timestamp DD/MM/YYYY hh:mm:ss (tag $numeric, $timestamp in table viewer)

Hope anybody can help me.

Thank you. !

[Nicole-Smith · Accepted solution]

Try this:

Where Timestamp(Timestamp#([DO DateTime],'DD/MM/YYYY hh:mm:ss')) < '$(varMinDateTime)'

Or this:

Where Timestamp(Timestamp#([DO DateTime],'DD/MM/YYYY hh:mm:ss')) < $(varMinDateTime)

[maxgro]

Temp:   

Load   

              min([DO DateTime]) as minDate,   

              max([DO DateTime]) as maxDate 

Resident $(vFactTable);     

Let varMinDateTime =Peek('minDate', 0, 'Temp');                            // number

Let varMaxDateTime =Peek('maxDate', 0, 'Temp');     

DROP Table Temp;   

trace varMinDateTime=$(varMinDateTime);

IF not IsNull(QvdCreateTime('$(vDiretorio)QVDs\$(vFactTable).qvd')) then 

Concatenate 

LOAD * 

FROM $(vDiretorio)QVDs\$(vFactTable).qvd (qvd) 

Where Timestamp#([DO DateTime]) < $(varMinDateTime)  ;           // compare number

End IF  ;

[vivek_niti]

Try converting the timestamp in Num at the where clause.

FROM $(vDiretorio)QVDs\$(vFactTable).qvd (qvd) 

Where Num(Timestamp(Timestamp#([DO DateTime]))) < Num($(varMinDateTime))

[h_demarco]

Still getting

Garbage after statement

Concatenate

LOAD *

FROM QVDs\Deliveries.qvd (qvd)

Where Timestamp#([DO DateTime]) < 42037,294247685

Trace retuned varMinDateTime=42037,294247685

But probably the error is due to my system "comma as decimal" as default. We are getting closer.

[h_demarco]

Thanks Nicole.

The solution was quotes for variable as you mentioned '$(varMinDateTime)'

Because my system default has comma as decimal, script was wrongly considering day fraction as a comma inside the code.

It would be awesome if all countries could have the same units default.

[maxgro]

and what about this?

Temp:

Load

              min([DO DateTime]) as minDate,

              max([DO DateTime]) as maxDate

Resident $(vFactTable);

SET ThousandSep=',';

SET DecimalSep='.';

Let varMinDateTime =Peek('minDate', 0, 'Temp');                            // number

Let varMaxDateTime =Peek('maxDate', 0, 'Temp');

DROP Table Temp;

trace varMinDateTime=$(varMinDateTime);

IF not IsNull(QvdCreateTime('$(vDiretorio)QVDs\$(vFactTable).qvd')) then

Concatenate

LOAD *

FROM $(vDiretorio)QVDs\$(vFactTable).qvd (qvd)

Where Timestamp#([DO DateTime]) < $(varMinDateTime)  ;          // compare number

End IF  ;

// ***** back to standard

SET ThousandSep='.';

SET DecimalSep=',';