topic Re: Time format in QlikView

Time format

Anonymous — Thu, 27 Jul 2017 13:39:28 GMT

Hello,

The format of the time in the files I load is not how I want it to be and I need to change it.

This is the format of the files:

- After I add "Time(Time#...." some rows is correct, but some are not. Ex. Orig time 74752 (07:47:52) --> 03:15:02

- I guess that when the time is not equal to "hhmmss" it becomes an issue.

- How can I add in the script that some rows in the file are in this format "hmmss" and should be presented as "hh:mm:ss"?

* And another question is regarding the field "Completed time". This is in another format where ex. 15.15 = 15:09 (60*0,15=9)

- I want it to be presented as the previous example "hh:mm:ss"

Thanks in advance!

Re: Time format

antoniotiman — Thu, 27 Jul 2017 13:48:18 GMT

HI,

Time(Time#(Num([Action Time],'000000'),'hhmmss'),'hh:mm:ss')

Regards,

Antonio

Re: Time format

sunny_talwar — Thu, 27 Jul 2017 13:51:40 GMT

This should also work

Time(Time#([Action Time],'hmmss'),'hh:mm:ss')

Re: Time format

Nagaraju_KCS — Thu, 27 Jul 2017 13:52:14 GMT

try this,

=interval(Avg(YourField)/86400,'hh:mm:ss')

Re: Time format

Anonymous — Fri, 28 Jul 2017 09:35:18 GMT

Thanks Antonio!

Re: Time format

Anonymous — Fri, 28 Jul 2017 10:23:33 GMT

Should this be in the script? Because I get an error when I add this to the script.

Re: Time format

sunny_talwar — Fri, 28 Jul 2017 11:02:34 GMT

If you got what you wanted, please close the thread by marking correct and helpful responses.

Best,

Sunny

Re: Time format

Anonymous — Fri, 28 Jul 2017 11:05:07 GMT

My first question is answered. Still need help with the second.

Re: Time format

sunny_talwar — Fri, 28 Jul 2017 11:10:13 GMT

May be this?

=Interval(Interval#(15.15*60, 'mm'), 'hh:mm:ss')

assuming 15.15 is 15 hours and 9 minutes

Re: Time format

sunny_talwar — Fri, 28 Jul 2017 11:10:42 GMT

Or more generally

=Interval(Interval#([Completed Time], 'mm'), 'hh:mm:ss')

Re: Time format

Anonymous — Fri, 28 Jul 2017 11:18:32 GMT

The numbers in the column dissapears when I load the script.

Re: Time format

sunny_talwar — Fri, 28 Jul 2017 11:22:47 GMT

How about this

=Interval(Interval#(Num#([Completed Time], '##,##', ',', '.')*60, 'mm'), 'hh:mm:ss')

Re: Time format

Anonymous — Fri, 28 Jul 2017 11:26:22 GMT

A little bit closer , but really weird that it didnt work for all rows?

Re: Time format

sunny_talwar — Fri, 28 Jul 2017 11:32:02 GMT

See if this is better

=Interval(Interval#(Num#([Completed Time], '##,##', ',', '.')*3600, 'ss'), 'hh:mm:ss')

Re: Time format

Anonymous — Fri, 28 Jul 2017 11:36:48 GMT

Thank you so much it works

Re: Time format

sunny_talwar — Fri, 28 Jul 2017 11:38:00 GMT

Awesome, please close the thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny