9 Replies Latest reply: Nov 29, 2011 3:25 PM by dhornsten RSS

    Colon

      Hi, I try to use an if expression to check if a time is = defined time.
      In the script I convert a time field that time (Date, "hh:mm ') as Time.
      When I'll check this in an if statement, I get errors in the colon.

       

      if (Time> = 02:00,.........)

       

      I've tried if (Time> = Time (02:00, 'hh: mm'),.....) but find no match.

       

      Does anyone know what I miss?

       


      Thanks,
      Daniel

        • Re: Colon
          Miguel Angel Baeyens de Arce

          Hi Daniel,

           

          If the time is part of a timestamp that stores a date as well, you will need to remove the date part. So for a value "29/11/2011 03:05", only time part ("03:05") will be

           

          =Time(Frac(Date#('29/11/2011 03:05', 'DD/MM/YYYY hh:mm')))
          

           

          Now it's considered as a dual value: a string, so you need to compare to '03:05' single quoted, or a numeric value where each hour is 1/24 (one day equals to 1). So your script will likely work when

           

          If(TimeField >= Time('02:00'), Yes part, No part)
          

           

          Hope that helps.

           

          Miguel

            • Re: Colon

              Hello.
              Does not use timestamp, use the time (DateValue, "hh: mm ') function.

              tmpLog:

              LOAD

              .

              .

              time(gpsdatetime, 'hh:mm') as Time

              .
              FROM

              Table;

               

              LogMod:

              LOAD *,
              If(Time >= Time('02:00'), 'Yes part', 'No part') as Test,

              Resident

              tmpLog

                • Re: Colon
                  John Witherspoon

                  So what's the format of gpsdatetime?  It sounds like a timestamp.  If so, you need to do what Miguel said to extract out the time portion, though I think like this:

                   

                  time(frac(timestamp#(gpsdatetime,'DD/MM/YYYY hh:mm')),'hh:mm')

                   

                  And it will, of course, depend on the format of your gpsdatetime field.

                   

                  I'd then do the comparison to a time or its numeric equivalent.  Here are some examples:

                   

                  if(Time >= maketime(2), 'Yes part', 'No part') as Test,
                  if(Time >= time#('02:00','hh:mm'), 'Yes part', 'No part') as Test,
                  if(Time >= 2/24, 'Yes part', 'No part') as Test,

                    • Re: Colon

                      hi.

                       

                      The field gpsdatetime is a data field from SQL Server. I create the Time field by the function time (gpsdatetime, 'hh: mm') as Time. Think it should go, have done it sooner!

                       

                      Test your example, but did not get anything out!

                       

                       

                      this is how fields like after I have run the script

                       

                      gpsdatetime = 2011-08-23 06:15:50

                      time = 06:15

                       

                       

                      / Daniel

                        • Re: Colon
                          John Witherspoon

                          As I said, "it will... depend on the format of your gpsdatetime field".  Your example data is 2011-08-28 06:15:50, which is format 'YYYY-MM-DD hh:mm:ss'.  So use that instead:

                           

                          time(frac(timestamp#(gpsdatetime,'YYYY-MM-DD hh:mm:ss')),'hh:mm')

                           

                          Time() is just a formatting function.  If you happen to have your timestamp format set to 'YYYY-MM-DD hh:mm:ss', then QlikView will realize that gpsdatetime is a timestamp, and time() will display it as a time.  But that's DISPLAY it as a time, not change it to a time.  That's why we're asking you to use the frac() function, which will remove the date portion of the timestamp.  Even then, it may not be quite what you want, as the seconds are still stored, just not displayed.  You may want to do something like this:

                           

                          time(time#(mid(gpsdatetime,12,5),'hh:mm'),'hh:mm') as Time

                           

                          That should strip off the date and the seconds, which may be what you're after.

                          • Re: Colon

                            There was nothing wrong with the comparison! It was wrong when I run if (Time> = 0:00 and Time <= 0:59, 'True', False) as test that causes trouble ....

                    • Colon
                      Christophe Brault

                      Hi,

                       

                      Can you paste your script ? I'm not sure to understand your problem.

                       

                       

                      If your field Time contain data as 12:00 you must write in your condition :

                       

                      if(Time>Time(12,00),true,false)