Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
h_demarco
Contributor III
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
Nicole-Smith

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)



View solution in original post

6 Replies
Nicole-Smith

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
MVP
MVP


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
Partner - Creator
Partner - Creator

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

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

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
MVP
MVP

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