topic Time out timestamp in QlikView

Time out timestamp

Wed, 16 Jul 2014 08:38:35 GMT

I would like to get the time out of my timestamp field.

in the format: HH:MM:SS

I can get the date out of it with the next expression:

=Date(Floor(emptyings_date_time))

Now I need the time part

In addition, I would also want to calculate the differece between each time out of the timestamp.

Re: Time out timestamp

kaushiknsolanki — Wed, 16 Jul 2014 08:42:17 GMT

Hi,

Try this.

Time (emptyings_date_time - Date(Floor(emptyings_date_time)))

Regards,

Kaushik Solanki

Re: Time out timestamp

Wed, 16 Jul 2014 08:49:06 GMT

That doesn't work.

I can't get the time out of the timestamp with: time(emptyings_date_time)

Is there an other way to get the time?

Re: Time out timestamp

kaushiknsolanki — Wed, 16 Jul 2014 08:51:24 GMT

Hi,

That may be because the Emptyings_date_time field wont be in date format.

You first convert it into Timestamp and then use the Time(Field) to get the time.

You can use the expression which I have given in earlier post to just get time.

Regards,

Kaushik Solanki

Re: Time out timestamp

Wed, 16 Jul 2014 08:55:42 GMT

How do I convert this field?

It looks like a timestamp and the date() function does work with it

Re: Time out timestamp

kaushiknsolanki — Wed, 16 Jul 2014 08:57:37 GMT

Hi,

If date works then time should also work.

Can you tell me what expression you are using.

And when you used the only I told you, what output you got?

Regards,

Kaushik Solanki

Re: Time out timestamp

Anonymous — Wed, 16 Jul 2014 08:57:59 GMT

Hi,

Try below,

Maketime(Hour(emptyings_date_time), Minute(emptyings_date_time),Second(emptyings_date_time))

Re: Time out timestamp

Wed, 16 Jul 2014 09:01:03 GMT

For the date I used:

=Date(Floor(emptyings_date_time))

If I use: Date(emptyings_date_time)

it doesn't work. So i think I need to add something like 'floor' to the time expression?

For the time I tried:

=Time(emptyings_date_time)

With the result: -

Your expression had the result: -

Re: Time out timestamp

Wed, 16 Jul 2014 09:01:52 GMT

Doesn't work unfortunatly

I only get: -

Re: Time out timestamp

kaushiknsolanki — Wed, 16 Jul 2014 09:05:13 GMT

Hi,

can you post your application with some sample data which replicates this issue.

Regards,

Kaushik Solanki

Re: Time out timestamp

Wed, 16 Jul 2014 09:11:21 GMT

It's an application with SQL connection, I don't know how to make a small example with sample data

Re: Time out timestamp

Anonymous — Wed, 16 Jul 2014 09:13:07 GMT

Hi,

It means the column "emptyings_date_time" is not in date format. You have to convert it first in date format before creating time field.

Re: Time out timestamp

Anonymous — Wed, 16 Jul 2014 09:15:35 GMT

Just export few rows will be enough to understand the problem area.

Re: Time out timestamp

Wed, 16 Jul 2014 09:18:51 GMT

I exported the Time table to excel and loaded it into a new qvw.

Re: Time out timestamp

Anonymous — Wed, 16 Jul 2014 09:35:36 GMT

Hi,

No issue in files. Getting below result,

Re: Time out timestamp

PradeepReddy — Wed, 16 Jul 2014 09:37:16 GMT

now it's coming perfectly...

Date(A) - Giving Date

Time(A) - Giving Time

so there might be some date format issue in your original application

Re: Time out timestamp

Wed, 16 Jul 2014 09:49:42 GMT

Apparently it does work, but only when I select one time from te timestamp field.

The date however always works.

I added two printscreens from my application.

The first one:

One city is selected and I want the hours to be filled in.

The second view:

I selected also one field from the timestamp table, now it fills in an hour.

The problem is that I want them to show like the dates in the first view.

Re: Time out timestamp

Anonymous — Wed, 16 Jul 2014 09:59:59 GMT

Hi,

Just to check, are you calculating the time field in front end? Because I could see there are few timestamps in each days so calculating this in frontend won't be giving you expected result. Try using min or max function in your existing logic or calculate this in backend.

Re: Time out timestamp

Wed, 16 Jul 2014 10:07:24 GMT

I'am calculating this in the front-end.

The timestamp is the exact day and moment when a container was emptied.

I want to show for each day al the hours that a container was emptied.

For the date I just did: date(emptyings_date_time)

Now I have a problem with still linking the times to a certain date.

I also need the first and the last time that a container was emptied each day.

How do I make the link to the day?