Hi Folks,
I'm having a real problem with date/time field.
I need to be able to calculate the difference (in minutes) between two times. When I load the data into Qlikview, it seems to be recognised only as text. The datetimes come from a text file and are stored in the following format:
| 1/2/2012 21:38:00 |
| 1/3/2012 7:59:00 |
| 1/3/2012 8:20:00 |
| 1/3/2012 8:22:00 |
If I look at the document properties -> tables tab, I can see that the field is tagged as $ascii, $text.
I am able to use
SubField(CHECKIN, ' ', 2) AS CHECKIN_TIME,
SubField(CHECKIN, ' ', 1) AS CHECKIN_DATE
This results in the field being recognised as time and date respectively. I can then calculate the difference timewise.
The problem is that some start finish combinations go past midnight and into the next day. Subtracting without the date included results in negative figures.
I've tried loading using Timestamp#(CHECKIN, 'D/M/YYYY hh:mm:ss'), but that doesn't seem to work either.
I'm sure there's something I'm missing....
