topic Re: Convert string into a decimal time in QlikView

Convert string into a decimal time

Mon, 06 Oct 2014 15:38:21 GMT

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

JonnyPoole — Mon, 06 Oct 2014 15:46:18 GMT

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

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

Mon, 06 Oct 2014 15:56:57 GMT

Thanx very much Jonathan!