Problem parsing datetime

Tue, 27 Nov 2012 02:03:23 GMT

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....

Re: Problem parsing datetime

Anonymous — Tue, 27 Nov 2012 02:07:05 GMT

Hi there,

What's not working with the Timestamp#() approach? Seems fine on my end, at least with the example timestamps you provided.

Re: Problem parsing datetime

Tue, 27 Nov 2012 02:21:57 GMT

You are quite right! I can now get it working.

topic Re: Problem parsing datetime in QlikView

Problem parsing datetime

Re: Problem parsing datetime

Re: Problem parsing datetime