# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for
Did you mean:
Contributor III

## How to convert the time into decimal and multiply with number

Hello experts,

Please can anyone help on this.

I have a duration field which is containing the data like below.

Ex:

00:02:55

1d 01:25:22

4d 00:06:55

For these above records i need convert in to decimal values and multiply with 24.

For 1d 01:25:22 and 4d -> i know that we need to split and multiply by 24 then we need to add both the values.

how to convert and multiply with duration and main challenging with 1d and 4d.

Attached the excel for reference.

1 Solution

Accepted Solutions
Champion III

Try this?

Decimal:

LOAD *, IF(IsNum(Duration), Duration*24, Num((Left(StrDuration,1)*24)+ (Subfield(StrDuration, ' ',2)*24)))  AS FinalDurationInt;

//LOAD *, Subfield(StrDuration, ' ',2) AS StrDurationTime;

IF(NOT ISNUM(Duration), Interval#(Replace(Duration, 'd',''), 'd h:mm:ss')) AS StrDuration,

Replace(Duration, 'd','') As Duration,

Required

FROM

[New Microsoft Excel Worksheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

3 Replies
Champion III

Try this?

Decimal:

LOAD *, IF(IsNum(Duration), Duration*24, Num((Left(StrDuration,1)*24)+ (Subfield(StrDuration, ' ',2)*24)))  AS FinalDurationInt;

//LOAD *, Subfield(StrDuration, ' ',2) AS StrDurationTime;

IF(NOT ISNUM(Duration), Interval#(Replace(Duration, 'd',''), 'd h:mm:ss')) AS StrDuration,

Replace(Duration, 'd','') As Duration,

Required

FROM

[New Microsoft Excel Worksheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

Contributor III
Author

Thanks vishwarath.

You are give a good suggestion to me,its working.

Master II