6 Replies Latest reply: Jul 23, 2015 2:26 PM by Massimo Grossi RSS

    Error on where clause with date criteria

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

        • Re: Error on where clause with date criteria
          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)



          • Re: Error on where clause with date criteria
            Massimo Grossi


            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  ;



              • Re: Error on where clause with date criteria
                Henrique 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.

                  • Re: Error on where clause with date criteria
                    Massimo Grossi

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

                • Re: Error on where clause with date criteria
                  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))