topic Re: Converting Timestamp format to seconds in QlikView

Converting Timestamp format to seconds

Bala_s — Fri, 25 May 2018 14:09:24 GMT

Hi Experts,

Need help here!!!

I have one column as AvgTime where i have values like below

1:06:00 AM // This is (hh:mm:ss tt Format)

:51:40 // This is (mm:ss Format)

:11 // This is (ss Format)

I need to present sum(AvgTime) in seconds, how can I convert all above formats to only seconds?

Please help me here to get output.

Regards,

Bala

Re: Converting Timestamp format to seconds

sunny_talwar — Fri, 25 May 2018 14:12:56 GMT

May be this

Interval(FieldName, 'ss')

Re: Converting Timestamp format to seconds

Bala_s — Fri, 25 May 2018 14:38:47 GMT

Hi Sunny,

Tried this but value becomes 0

Re: Converting Timestamp format to seconds

sunny_talwar — Fri, 25 May 2018 14:43:09 GMT

Can you share an image of where you are doing this

Re: Converting Timestamp format to seconds

Bala_s — Fri, 25 May 2018 14:51:58 GMT

Please find below images 1st is Script part and 2nd is list box..

Re: Converting Timestamp format to seconds

vvira1316 — Fri, 25 May 2018 14:59:31 GMT

for following sample scenario what you are expecting as values

1:06:00 AM // This is (hh:mm:ss tt Format)

3960 or 00

:51:40 // This is (mm:ss Format)

3070 or 40

:11 // This is (ss Format)

Re: Converting Timestamp format to seconds

sunny_talwar — Fri, 25 May 2018 15:00:53 GMT

May be try this

Interval(Alt(Num([Avg Speed Ans]), Num(Time#([Avg Speed Ans], 'hh:mm:ss')), Num(Time#([Avg Speed Ans], ':mm:ss')), Num(Time#([Avg Speed Ans], ':ss'))), 'ss') as [Avg Speed Ans]

Re: Converting Timestamp format to seconds

Bala_s — Fri, 25 May 2018 15:40:55 GMT

Thanks Sunny for help...

Pls find below snapshot, might be calculation for seconds is wrong..

Re: Converting Timestamp format to seconds

sunny_talwar — Fri, 25 May 2018 15:42:12 GMT

What is your expected output my friend?

Re: Converting Timestamp format to seconds

Bala_s — Fri, 25 May 2018 15:46:38 GMT

My scenario is like below

Ex: Data is 01:06:00 AM

I should get convert above into seconds....(3960 seconds) like wise I need to get for all above example cases.

Re: Converting Timestamp format to seconds

sunny_talwar — Fri, 25 May 2018 15:54:06 GMT

Can you check the number's tab of your list box that you are showing? Can you make sure that you have not selected 'Override Document Settings'

Re: Converting Timestamp format to seconds

Bala_s — Mon, 28 May 2018 07:30:06 GMT

Hi Sunny,

Yes, 'Override Document Settings' is not selected...

Re: Converting Timestamp format to seconds

sunny_talwar — Mon, 28 May 2018 12:13:13 GMT

Would you be able to share a qvw file to show where this isn't working?