topic Re: Time Conversion in QlikView

Time Conversion

pgalvezt — Fri, 08 Apr 2016 19:52:17 GMT

Hi,

How can I convert time (excel) in Number Int, And Number with 2 decimals?

I attached an Excel file for better comprenhension...

Thanks,

Re: Time Conversion

sunny_talwar — Fri, 08 Apr 2016 19:56:19 GMT

This seems to be working:

Table:

LOAD Time(R) as Time,

FROM

[Test (1).xlsx]

(ooxml, embedded labels, table is Hoja1);

Table:

LOAD Num(Time(R), '##.##') as TimeNum,

Time(R) as Time,

FROM

[Test (1).xlsx]

(ooxml, embedded labels, table is Hoja1);

Re: Time Conversion

swuehl — Fri, 08 Apr 2016 23:43:00 GMT

Hi Pablo,

what is your expected result? I am sorry, I don't really understand 'Number Int, And Number with 2 decimals'

(hence, for future requests, please also post your expected results).

I assume you already know

Get the Dates Right

and how date and time values are correctly read into QlikView and that date and time values have a numeric representation, just like in excel. Your R values already have numeric representation in your source, so you can just read the value in.

The integer (int) part of your time values would be returned by

Floor(R)

(all zero for your sample) and the fractional part by

Frac(R)

You can use number formatting like shown by Sunny using num() function.

Num(R,'#.00','.',',')

You can also use time functions to return the hour and minutes, if that's what you are looking for

Hour(R)

Minute(R)

Second(R)

or time formating function Time(R). Best to just have a look at the formatting functions in general

On Format Codes for Numbers and Dates

The Date Function

Regards,

Stefan

Re: Time Conversion

pgalvezt — Mon, 11 Apr 2016 21:56:28 GMT

Hi swuehl

I attached a test file where I show you what I expect of the final result.

Thanks!!

Re: Time Conversion

sunny_talwar — Mon, 11 Apr 2016 22:07:20 GMT

May be this:

Table:

LOAD Hour(R) & '.' & Round((Num(Minute(R))*100/60)) as Required,

FROM

[Test (1).xlsx]

(ooxml, embedded labels, table is Hoja1);

Re: Time Conversion

MarcoWedel — Mon, 11 Apr 2016 22:43:48 GMT

Hi,

maybe like this?

tabTime:
LOAD R,
     Num(R*24,'0.00') as [What I expect]
FROM [https://community.qlik.com/servlet/JiveServlet/download/1015749-220422/Test.xlsx] (ooxml, embedded labels, table is Hoja1);

hope this helps

regards

Marco

Re: Time Conversion

pgalvezt — Tue, 12 Apr 2016 18:59:43 GMT

Thank you all!