evaluating time stamp

[#1]

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:

if

(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

[#2]

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

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

[#3]

Hello,

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

Any other ideas?

Maria

[Miguel_Angel_Baeyens · Accepted solution]

Hello Maria,

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

Should work as you expect.

Hope that helps.

[#5]

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

[Miguel_Angel_Baeyens]

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.

[#7]

Miguel,

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

Maria