Timestamp format

[#1]

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

[sunny_talwar]

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);

[#3]

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

[sunny_talwar]

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')

[#5]

Hi Sunny,

Its not working for me.

Attached the sample qvw and sample data for the same.

Regards,

Anjali Gupta

[sunny_talwar]

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);

[Peter_Cammaert · Accepted solution]

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

[#8]

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

Regards,

Anjali Gupta

[#9]

Thanks sunny for all your efforts.

Regards,

Anjali Gupta