Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

evaluating time stamp

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

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

Hello Maria,

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


Should work as you expect.

Hope that helps.

View solution in original post

6 Replies
Not applicable
Author

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

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

Not applicable
Author

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

Hello Maria,

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


Should work as you expect.

Hope that helps.

View solution in original post

Not applicable
Author

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.

Not applicable
Author

Miguel,

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

Maria