topic Re: Remove Seconds from a Time Field in QlikView

Remove Seconds from a Time Field

mphekin12 — Thu, 14 Jul 2016 11:44:48 GMT

Hello,

I am having some issues with what seems like a very easy problem. I want to remove the seconds (no rounding) from a time field. I am currently using the following code but this doesn't seem to be working:

time(frac(END_DATE), 'h:mm TT') as END_TM

The reason I know it's not working is that there are duplicate times when I set up a List Box:

If I override the document settings in the Number tab and select 'Time' as the data type, the list box displays the seconds:

Can someone tell me what I need to do to my code above to remove/truncate the seconds from the time? Again, I do not want to round.

Thank you!

Re: Remove Seconds from a Time Field

CELAMBARASAN — Thu, 14 Jul 2016 11:51:43 GMT

Try flooring the secs

Time(Floor(Frac(END_DATE), 1/(24*60)), 'h:mm TT') as END_TM

Re: Remove Seconds from a Time Field

mphekin12 — Thu, 14 Jul 2016 12:02:18 GMT

Celambarasan,

Thanks for the quick reply. I have already tried that code and although the seconds were removed from the timestamp, the result didn't give the expected results in all situations. Please see the highlighted records for examples. In every highlighted case, I was expecting '12:02 AM' as the result.

Any other ideas?

Re: Remove Seconds from a Time Field

sunny_talwar — Thu, 14 Jul 2016 12:21:45 GMT

I would assumed the above to have worked. Would you be able to share a sample where this isn't working?

Re: Remove Seconds from a Time Field

mphekin12 — Thu, 14 Jul 2016 12:26:12 GMT

Sunny,

Please see the screenshot in my previous post. There are several records where the time is 12:02 AM that are changed to 12:02 AM if I use the code provided by Celambarasan. I have highlighted them in yellow so you can find them easily.

Thank you!

Re: Remove Seconds from a Time Field

sunny_talwar — Thu, 14 Jul 2016 12:28:21 GMT

I see that, would you be able to share a screenshot of your script where you create End Time field?

Re: Remove Seconds from a Time Field

mphekin12 — Thu, 14 Jul 2016 12:30:06 GMT

Here you go:

Re: Remove Seconds from a Time Field

Chanty4u — Thu, 14 Jul 2016 12:33:02 GMT

try this

,date(floor(timestamp#(Endtime,'MM/DD/YYYY hh:mm:ss'),'h:mm TT')) as record_date

,time(frac (timestamp#(Endtime,'MM/DD/YYYY hh:mm:ss'),'h:mm TT')) as record_time

Re: Remove Seconds from a Time Field

CELAMBARASAN — Thu, 14 Jul 2016 12:40:11 GMT

Yes, there is a problem in floor function, in most of the cases this seems not working.

As a work around you can try this

Time#(Text(Time(Frac(END_DATE), 'h:mm TT')), 'h:mm TT') as END_TM

Re: Remove Seconds from a Time Field

mphekin12 — Thu, 14 Jul 2016 12:41:10 GMT

Chanty 4u,

I had to change your code around a little because the syntax was wrong. I changed it to:

Unfortunately no data was returned with this code.

Thanks for your help though!

Re: Remove Seconds from a Time Field

sunny_talwar — Thu, 14 Jul 2016 12:42:10 GMT

It seems to be working well for me:

LET vVAR = Num(Now());

Table:

LOAD LOCATION_END_DATE,

Time(Floor(Frac(LOCATION_END_DATE), 1/(24*60)), 'h:mm TT') as LOCATION_END_TM;

LOAD TimeStamp($(vVAR) + MakeTime(0, 0, RecNo() - 1)) as LOCATION_END_DATE

AutoGenerate 60;

Re: Remove Seconds from a Time Field

mphekin12 — Thu, 14 Jul 2016 12:45:17 GMT

Sunny,

Thanks for the example. I did find one case in your example where the end time doesn't match the end date/time:

Any thoughts on this?

Re: Remove Seconds from a Time Field

sunny_talwar — Thu, 14 Jul 2016 12:47:12 GMT

Use the new approach that celambarasan‌ mentioned below. I think this is attributed to rounding errors:

Rounding Errors

Re: Remove Seconds from a Time Field

jonathandienst — Thu, 14 Jul 2016 12:48:48 GMT

I think you need to use Round() rather than Floor() in your expression. Round will remove the seconds and go to the nearest minute, while Floor() simply removes the seconds, effectively always rounding down.

Re: Remove Seconds from a Time Field

mphekin12 — Thu, 14 Jul 2016 12:50:42 GMT

Thank you Celambarasan, this new code seems to be working correctly! I appreciate your help.

Re: Remove Seconds from a Time Field

Anil_Babu_Samineni — Thu, 14 Jul 2016 12:51:02 GMT

How about this

timestamp(floor(LOCATION_END_DATE*1440)/1440 as Location_END_TM

timestamp#( timestamp( LOCATION_END_DATE, 'DD.MM.YYYY hh:mm'), 'DD.MM.YYYY hh:mm') as Location_END_TM

Re: Remove Seconds from a Time Field

mphekin12 — Thu, 14 Jul 2016 12:51:49 GMT

Jonathan,

Thanks for your response, but as stated above, I do not want to round. I just need to truncate the seconds from the timestamp.

Re: Remove Seconds from a Time Field

mphekin12 — Thu, 14 Jul 2016 12:52:42 GMT

Thank you Sunny. It does appear that Celambarasan's method works.