topic Re: Time calculation in QlikView

Time calculation

shamitshah — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the following which have been converted to time, but I need a formula in Qlik to do the same:

Action Time Converts to the following time

48240 13.24.00

48249 13.24.09

The formula is as follows with respect to the first example above:

Hour worked out as 48240/3600 = 13.4 (hour is 13)

Minutes - remainder/60 = 1440/60 = 24 (minutes is 24)

Seconds will 00 as there is no remainder

Any idea how I can incorporate the above in Qlik?

Thanks

Shamit

Re: Time calculation

hemanthaanichet — Wed, 23 Nov 2016 05:01:15 GMT

Hi Shamit,

Can you conform me whether that Action Time is hours/minutes/seconds,

I think your requirement is Action Time has converts to HH:MM:SS or soomething else

Would you able to provide sample data that will be more helpful to work on it

Regards

Hemanth

Re: Time calculation

shamitshah — Wed, 23 Nov 2016 05:22:42 GMT

Hi Hemanth,

That's correct - Action time converts to HH.MM.SS

I have attached a sample file.

Thanks

Re: Time calculation

hemanthaanichet — Wed, 23 Nov 2016 05:33:38 GMT

Hi Shamit,

One quick doubt is that action time is hours/mintues/seconds

Regards

Hemanth

Re: Time calculation

eromiyasimon — Wed, 23 Nov 2016 05:49:51 GMT

hi Shamit,

use the below expression to get the value as expected

=num(ActionTime/3600,'##')&':'&num(mod(ActionTime,3600)/60,'##')&':'& num(mod(mod(ActionTime,3600),60),'00')

refer the attached QVW

hope this helps

Re: Time calculation

shamitshah — Wed, 23 Nov 2016 05:51:44 GMT

Hi Hemanth,

I am not sure what you mean. The Action Time (this is the time) gets converted to the actual time in HH.MM.SS

Hope that helps.

Thanks

Shamit

Re: Time calculation

tresB — Wed, 23 Nov 2016 05:54:35 GMT

Try like:

Timestamp(time#(ActionTime,'sssss'), 'hh:mm:ss')

Re: Time calculation

Anonymous — Wed, 23 Nov 2016 05:54:40 GMT

Hi Shamit,

Use following in script for required result

Time(Time#([Action Time],'ss'),'h:m:ss') AS True_Action_Time

above function is giving following result :

13:24:00

13:24:09

Thanks,

Re: Time calculation

shamitshah — Wed, 23 Nov 2016 05:57:10 GMT

Hi Eromiya,

Is there anyway I can include that in the script so that the time appears as per your formula?

Thanks

Shamit

Re: Time calculation

eromiyasimon — Wed, 23 Nov 2016 06:00:04 GMT

use poojas expression in Script if you want as script

Time(Time#([Action Time],'ss'),'h:m:ss') AS True_Action_Time

Re: Time calculation

shamitshah — Wed, 23 Nov 2016 06:22:23 GMT

Hi Pooja,

Please attach your solution.

Thanks

Shamit

Re: Time calculation

eromiyasimon — Wed, 23 Nov 2016 06:30:21 GMT

hi shamit,

i have loaded in script and attached you the QVW

Re: Time calculation

shamitshah — Wed, 23 Nov 2016 06:44:06 GMT

Thanks!

Re: Time calculation

Anil_Babu_Samineni — Wed, 23 Nov 2016 07:20:38 GMT

Can you try this for me

LOAD *, Time(Time#([Action Time],'sssss'),'h:m:ss') as Converts to the following time INLINE [

Action Time

48240

48249

];

And take table box, With the names are [Action Time] & [Converts to the following time] and then see.