Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
sumanyedla121
Contributor III
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
vishsaggi
Champion III
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;

LOAD Sales,

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

View solution in original post

3 Replies
vishsaggi
Champion III
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;

LOAD Sales,

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

sumanyedla121
Contributor III
Contributor III
Author

Thanks vishwarath.

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

qlikviewwizard
Master II
Master II

Hi sumanyedla121

Happy to hear. Please close the thread by marking Correct Answer.