Time Conversion

[pgalvezt]

Hi,

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

I attached an Excel file for better comprenhension...

Thanks,

[sunny_talwar]

This seems to be working:

Table:

LOAD Time(R) as Time,

  R

FROM

[Test (1).xlsx]

(ooxml, embedded labels, table is Hoja1);

Table:

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

  Time(R) as Time,

  R

FROM

[Test (1).xlsx]

(ooxml, embedded labels, table is Hoja1);

[swuehl]

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

[pgalvezt]

Hi swuehl

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

Thanks!!

[sunny_talwar · Accepted solution]

May be this:

Table:

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

  R

FROM

[Test (1).xlsx]

(ooxml, embedded labels, table is Hoja1);

[MarcoWedel]

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

[pgalvezt]

Thank you all!