Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Convert string into a decimal time

Hello everyone!

I have a string field filled with values like this:

72 Hrs  0 Min

90 Hrs  53 Min

99 Hrs  0 Min

Hrs   Min

225 Hrs  0 Min

0 Hrs  58 Min

1520 Hrs  53 Min


I need to convert it to decimal format to make furter calculations

Somebody know how to do it?


Thanx very much!


Pablo

1 Solution

Accepted Solutions
jpe
Not applicable

Re: Convert string into a decimal time

It can be done with regular string functions.  Not sure what to do with the last row... what do you want ?

Capture.PNG.png

Load

  *,

  Hours + Minutes/60 as HourInDecimals;

LOAD

  A as Time,

  num(left(A, Index(A,'Hrs')-2)) as Hours,

  num(mid(A, Index(A,'Hrs') + 5 , Index(A,'Min')- (Index(A,'Hrs')+5)))  as Minutes

FROM

(ooxml, no labels, table is Sheet1);

2 Replies
jpe
Not applicable

Re: Convert string into a decimal time

It can be done with regular string functions.  Not sure what to do with the last row... what do you want ?

Capture.PNG.png

Load

  *,

  Hours + Minutes/60 as HourInDecimals;

LOAD

  A as Time,

  num(left(A, Index(A,'Hrs')-2)) as Hours,

  num(mid(A, Index(A,'Hrs') + 5 , Index(A,'Min')- (Index(A,'Hrs')+5)))  as Minutes

FROM

(ooxml, no labels, table is Sheet1);

Not applicable

Re: Convert string into a decimal time

Thanx very much Jonathan!