1/26/2009 12:00:00 AM
But the only relevant data is:
1/26/2009
Because you really don't care (and it wasn't recorded) what time the employee started. The best way to deal with this is to have the field brought into the app stripped of the time stamp.
My philosophy? A hundred-dollar shine on a three-dollar pair of shoes.
So let's fix it on the front end. It's easy.
SubField
If we create a simple table with Employee Name and Employee Start Date as dimensions, you will get something like:
And that looks like crap (technical term). Much better would be:
We'll use the function 'SubField' to strip the time stamp from the field.
SubField returns part of the field. There are three parts to the function:
- Field: The field you want to chop up
- Delimiter: Tells Qlikview where to chop up the field
- Position: Tell Qlikview which part you want to keep
The data in Employee Start Date has three spaces:
1/26/2009 12:00:00 AM
Which we will use as the delimiters. Since we only want the date returned, we enter '1' for the position. Entering '2' would bring back '12:00' and entering '3' would bring back 'AM'.
Formatting
If you follow the instructions above, you'll probably be cussing right now. Your 'date' column will bring back a bunch of 5-digit numbers. That's because Qlikview stores dates as numbers, then converts them to a date format. To bring back that format, simply wrap the SubField statement in a Date function:
=Date (SubField ([Employees Start Date], ], ' ', 1))
What about the good 'ol floor function
ReplyDeleteTIP 4: USE THE ROUNDING FUNCTIONS
The date field in the source data is often not just a date, but instead a timestamp
corresponding to a specific time during the day. The date serial number will then not be
an integer. For instance, the time 6 pm 1/1/2012 corresponds to the date serial number
40909.75.
In such a case it is not enough to use the date function to remove hours and minutes from
the formatting. Though formatting the timestamp as a date will hide the time from being
displayed, the fractional part of the serial number will still be there and the field may give
incorrect results in comparisons.
Instead a rounding function must be used to make the additional 0.75 from the numeric
value disappear, i.e.:
Date( Floor( Date ), 'YYYY-MM-DD') as Date
Another case where a rounding function is good is when the date is a key field linking
two tables. If the field value is a timestamp where you have a time of the day other than
midnight – 00:00:00 – then this value will not link to a date in another table even if you
have formatted it as a date: The string part of the dual format is not used as key if there
is a numeric value. The numeric value is always used as key. Hence it is not enough that
two numeric values are formatted exactly the same. If you want to use a date as a key, you
should use the integer part of the timestamp and omit the information about time of the day.
Example:
You have a timestamp, e.g. ‘2012-01-28 08:32:45’ in your transaction table and you want to
link this to a master calendar table containing dates. One correct way to load this key could be
Date( Floor( Timestamp#( Created, 'YYYY-MM-DD hh:mm:ss')), 'M/D/YYYY')
as CreatedDate
In addition to the key CreatedDate, other fields could also be created to show the fractional
part of the timestamp
Timestamp#( Created, 'YYYY-MM-DD hh:mm:ss') as CreatedTimestamp,
Time( Frac( Timestamp#( Created, 'YYYY-MM-DD hh:mm:ss')), 'hh:mm:ss')
as CreatedTime,
Also note that the function Frac() is used to remove the integer part of the number for
the field that only contains the time information
from http://community.qlikview.com/servlet/JiveServlet/previewBody/3102-102-3-4191/DateFields-TechBrief-US_v2.pdf
Thanks, Mike! One of my favorite/most-hated things about Qlikview is the many ways to solve a problem.
Delete