Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
h_demarco
New Contributor III

Error on where clause with date criteria

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

1 Solution

Accepted Solutions

Re: Error on where clause with date criteria

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)



6 Replies

Re: Error on where clause with date criteria

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)



MVP
MVP

Re: Error on where clause with date criteria


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
Contributor

Re: Error on where clause with date criteria

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
New Contributor III

Re: Error on where clause with date criteria

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
New Contributor III

Re: Error on where clause with date criteria

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.

MVP
MVP

Re: Error on where clause with date criteria

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=',';

Community Browser