topic evaluating time stamp in QlikView

evaluating time stamp

Thu, 10 Mar 2011 21:01:35 GMT

Hello,

my input file has a time stamp field in the format : '0:05:55', '2:15:33', '22:14:55' etc.

I am creating groupings based on these time stamps, by doing an If statement like this:

(time >= '00:00:00' and time <= '06:00:00' ,'Overnight' ,

if (time > '06:00:00' and time <= '09:00:00' ,'Early Morning'

This IF statement works for all time stamps that have a 2 digit format for the hour, but not for time stamps with only 1 digit for the hour ( ex: 2:15:33 instead of 02:15:33). I changed the format in the If statement to >=0:00:00 and <=6:00:00, but that produced totally wrong results.

Do I need to left pad with 0 those records in order to make this work or is there a better solution to this?

Thanks!

Maria

evaluating time stamp

Thu, 10 Mar 2011 22:10:45 GMT

change your timestamp to a time format while loading, for example:

time#(time,'hh:mm:ss') as Timestamp

evaluating time stamp

Thu, 10 Mar 2011 22:48:59 GMT

Hello,

I thought that would work for sure, but No - the format is still the same, no zero in front.

Any other ideas?

Maria

evaluating time stamp

Miguel_Angel_Baeyens — Thu, 10 Mar 2011 23:03:42 GMT

Hello Maria,

=Time('0:05:55', 'hh:mm:ss')

Should work as you expect.

Hope that helps.

evaluating time stamp

Thu, 10 Mar 2011 23:59:22 GMT

Thanks Miguel, that worked!

What exactly is the difference between all the functions with the #sign and the same functions without it?

I read thru the Help Guide and looked at the examples, but I still don't clearly understand the difference.

Maria

evaluating time stamp

Miguel_Angel_Baeyens — Fri, 11 Mar 2011 00:40:43 GMT

Hello Maria,

Functions with # convert a non-numeric value to a value interpretable by QlikView (usually, numeric).

For example, any date value in QlikView is a numeric (integer or decimal) value, representing days (if decimal, then days and hours-fractions of days).

Say my default configuration is for representing these numeric values to 'DD/MM/YYYY' (as set at the beginning of the script, stored in the variable "DateFormat" that usually follows the regional settings of your operating system) but I get 'D.MMM.YY' which is not numeric nor interpretable as such, therefore a literal (string).

I likely need first to "convert" (evaluate) the second value into a numeric date value:

Date#('3.jan.11', 'D.MMM.YY')

Internally, although displayed as a string of characters, this value is now interpreted as numeric:

Num(Date#('3.jan.11', 'D.MMM.YY'))

But instead of a plain number, I want tell QlikView to represent this number as a date so it can be used in date functions and aggregations:

Date(Date#('3.jan.11', 'D.MMM.YY'))

It's not what you see, rather that how QlikView interprets these values. And QlikView always interpret any value as either numeric or literal (string).

My view, in short, is that # functions tell how to interpret the value. Non # functions say how to represent the value.

Hope this makes some sense.

evaluating time stamp

Fri, 25 Mar 2011 16:12:36 GMT

Miguel,

thank you very much for the explanation. It is very helpful.

Maria