Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
Hi Christophe,
Maybe it's because the regional local settings, but if you want Time() return "12:00" by passing a numeric value instead of a string, then you need to do
=Time(0.5)
For QlikView, 1 equals to the whole day, meaning 24 hours. 12 is one half, so instead of 1, you need to use 0.5.
Hope that helps.
Miguel
Yes your right,
In my expression, i must use maketime to create a Time With number.
Thanks
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
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,
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
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.
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 ....