topic Re: Colon in QlikView

Colon

Tue, 29 Nov 2011 10:02:36 GMT

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 — Tue, 29 Nov 2011 10:29:55 GMT

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

Colon

christophebrault — Tue, 29 Nov 2011 10:33:11 GMT

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)

Re: Colon

Miguel_Angel_Baeyens — Tue, 29 Nov 2011 10:45:51 GMT

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

Re: Colon

christophebrault — Tue, 29 Nov 2011 10:51:29 GMT

Yes your right,

In my expression, i must use maketime to create a Time With number.

Thanks

Re: Colon

Tue, 29 Nov 2011 18:49:49 GMT

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

johnw — Tue, 29 Nov 2011 18:59:44 GMT

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

Tue, 29 Nov 2011 20:14:50 GMT

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

johnw — Tue, 29 Nov 2011 20:22:53 GMT

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

Tue, 29 Nov 2011 20:25:25 GMT

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