Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

Anonymous
Not applicable
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.