topic Re: Timestamp format in QlikView

Timestamp format

Mon, 28 Dec 2015 18:31:47 GMT

Hi all,

I have a field name Time which contains the data as 20.30, 8.00, 9.00 which means 20:30:00, 8:00:00 and 9:00:00 respectively in the format 'h:mm:ss'.

How to get the value from 'h.mm' format to 'h:mm:ss' :

h.mm	to	h:mm:ss
20.30	=>	20:30:00
7.00	=>	7:00:00
8.00	=>	8:00:00
16.40	=>	16:40:00

Please help me on this.

Regards,

Anjali gupta

Re: Timestamp format

sunny_talwar — Mon, 28 Dec 2015 18:34:05 GMT

Try this:

Time(Time#(YourCurrentTimeField, 'h.mm'), 'h:mm:ss')

UPDATE: Sample qvw file attached

Script:

Table:

LOAD h.mm as OldFormat,

Time(Time#(h.mm, 'h.mm'), 'h:mm:ss') as NewFormat

FROM

[https://community.qlik.com/thread/197820]

(html, codepage is 1252, embedded labels, table is @1);

Re: Timestamp format

Mon, 28 Dec 2015 18:39:23 GMT

Hi Sunny,

Thanks for the quick response but it didn't worked.

Tried the same with Date(Date#()) also.

What it gives is:

h.mm	to	h:mm:ss
20.30	=>	20:03:00
7.00	=>	-
8.00	=>	-
16.40	=>	16:04:00

Is there any other solution?

Regards,

Anjali Gupta

Re: Timestamp format

sunny_talwar — Mon, 28 Dec 2015 18:41:06 GMT

I have attached the sample above. It seems to be working in the sample. Can you check the sample and see if you might have missed something?

Time(Time#(YourCurrentTimeField, 'h.mm'), 'h:mm:ss')

Re: Timestamp format

Mon, 28 Dec 2015 18:50:08 GMT

Hi Sunny,

Its not working for me.

Attached the sample qvw and sample data for the same.

Regards,

Anjali Gupta

Re: Timestamp format

sunny_talwar — Mon, 28 Dec 2015 19:15:54 GMT

Give this a shot:

LOAD Time1,

Time((SubField(Time1, '.', 1) * 60 + Alt(SubField(Time1, '.', 2) * 10, 0))/(60*24), 'h:mm:ss') as New_Time

FROM

Sample_Time.xlsx

(ooxml, embedded labels, table is Sheet1);

Re: Timestamp format

Peter_Cammaert — Mon, 28 Dec 2015 19:20:19 GMT

Anjali,

why... do you specify timestamps as numerical values while they're not? And in Excel of all places?

What happens in your case is this:

Excel passes those timestamps as binary values. The formatting doesn't matter; they're numbers.

When a number like 7.30 is passed to QlikView, it comes out as 7.30 and you can treat it as a timestamp with minutes and seconds.

When a number like 6.00 is passed to QlikView, it comes out as 6.00 but the text value is an integer 6 without fraction. Therefor the Time#(Time1, 'h.mm') call will fail and return a NULL value because this is a string parsing function.

Have a look at your own table box. All timestamps with seconds are converted (wrongly but that doesn't matter right now) and those without a fraction do fail.

You could solve this by converting Sunny's formula into something like

Time(Time#(num(Time1, '0.00'), 'h.mm'), 'h:mm:ss')

to force seconds in all cases, but I suggest you better use real timestamps in Excel. That way you'll avoid a lot of conversion mess. Don't make it any more difficult than necessary.

Best,

Peter

Re: Timestamp format

Tue, 29 Dec 2015 05:49:17 GMT

Thanks a lot Peter.. for giving the understanding and also to solve my problem.

Regards,

Anjali Gupta

Re: Timestamp format

Tue, 29 Dec 2015 05:49:59 GMT

Thanks sunny for all your efforts.

Regards,

Anjali Gupta