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?
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:
Internally, although displayed as a string of characters, this value is now interpreted as numeric:
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:
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.